using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using System.Management;

namespace AnywhereTS
{
    class DatabaseSupport
    {
        enum VersionCheck
        {
            Failed = 0, Equal, DatabaseIsMoreNew,
            DatabaseIsOlder, DatabaseNotFound
        };

        private SqlConnection sqlCon = new SqlConnection();
        private SqlCommand sqlCmd = new SqlCommand();

        const string SQL_BROWSER_SERVICE_NAME = "SQLBrowser";

        public static string InstanceName = Properties.Settings.Default.atsConnectionString.Substring(Properties.Settings.Default.atsConnectionString.IndexOf(@"\"), Properties.Settings.Default.atsConnectionString.IndexOf(@";")).Replace(@"\", "").Replace(@";", "");
        public bool SetupDatabase()
        {
            bool bContinue = false;

            // Create a connection to SQL Server
            try
            {
                sqlCon.ConnectionString = Properties.Settings.Default.atsConnectionString;
                sqlCon.Open();
            }
            catch (SqlException sql_ex)
            {
                MessageBox.Show(string.Format("Fail to connect to SQL Server Instance: {0}\n", InstanceName) + sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
                return bContinue;
            }
         
            // Now that you are connected to Express, check the database versions

            Version databaseVersion; // The current version of the database

            switch (CheckVersion(out databaseVersion))
            {
                case (int)VersionCheck.Equal:
                    {
                        bContinue = true;
                        break;
                    }
                case (int)VersionCheck.Failed:
                    {
                        bContinue = false;
                        break;
                    }
                case (int)VersionCheck.DatabaseIsOlder:
                    {
                        switch (databaseVersion.ToString())
                        {
                            // Run the apropriate upgdrade script(s)
                            case "1.0.0.0":
                                {   // Current database is version 1.0.0.0, update to 1.0.0.1
                                    bContinue = RunScript(Resource1.UpdateDatabase1.ToString());
                                    goto case "1.0.0.1"; // Continue and upgrade one more step
                                }
                            case "1.0.0.1":
                                {   // Current database is version 1.0.0.1, update to 1.0.0.2
                                    bContinue = RunScript(Resource1.UpdateDatabase2.ToString());
                                    goto case "1.0.0.2"; // Continue and upgrade one more step
                                }
                            case "1.0.0.2":
                                {   // Current database is version 1.0.0.2, update to 1.0.0.3
                                    bContinue = RunScript(Resource1.UpdateDatabase3.ToString());
                                    break;
                                }

                            
                            default:
                                {
                                    MessageBox.Show("Error: Not able to upgrade database (51188)");
                                    break;
                                }
                        }
                        break;
                    }
                case (int)VersionCheck.DatabaseIsMoreNew:
                    {
                        bContinue = false;
                        break;
                    }
                case (int)VersionCheck.DatabaseNotFound:
                    {
                        //Run the creation script
                        bContinue = RunScript(Resource1.CreateDatabase.ToString());
                        if (bContinue)
                        {
                            // Set up file access rights for remote desktop users
                            SetDatabaseRights(); 
                        }

                        break;
                    }
                default:
                    {
                        bContinue = false;
                        break; 
                    }

            }
            sqlCon.Close();
            sqlCon.Dispose();
            sqlCmd.Connection.Close();
            sqlCmd.Connection.Dispose(); 
            return bContinue;
        }
        
        // Run a SQL script (to create or update a database)
        public bool RunScript(string strFile)
        {
            string[] strCommands;
            strCommands = ParseScriptToCommands(strFile);
            try
            {
                if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
                
                sqlCmd.Connection = sqlCon;

                foreach (string strCmd in strCommands)
                {
                    if (strCmd.Length > 0)
                    {
                        // Substitute database directory with the decided one.
                        sqlCmd.CommandText = strCmd.Replace("[DataDir]",ProSupport.strDatabasePath);
                        sqlCmd.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException sql_ex)
            {
                MessageBox.Show(sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
                return false;
            }

            return true;
        }
        
        
        // Check the version of the datbase
        public int CheckVersion(out Version vDb)
        {
            //Get Version information from application
            Version v=new Version(ATSGlobals.strDatabaseVersion);
            vDb = new Version("0.0.0.0"); // Assign a default value for version
            try
            {

                string strResult;

                //Verify that the AnywhereTS database exists
                sqlCmd = new SqlCommand("select count(*) from master..sysdatabases where name='AnywhereTS'", sqlCon);
                strResult = sqlCmd.ExecuteScalar().ToString();
                
                if (strResult == "0")
                {

                    return (int)VersionCheck.DatabaseNotFound;
                }

                try
                {
                    sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
                    strResult = (string)sqlCmd.ExecuteScalar();
                }
                catch
                {   // Assume we could not open the physical file 
                    //Drop the database
                    sqlCmd = new SqlCommand("IF EXISTS(SELECT * FROM sysdatabases WHERE name='AnywhereTS')DROP DATABASE AnywhereTS", sqlCon);
                    strResult = (string)sqlCmd.ExecuteScalar();
                    return (int)VersionCheck.DatabaseNotFound;                        
                }
                vDb = new Version(strResult);
          
                sqlCon.Close();

                if (vDb == v)
                    return (int)VersionCheck.Equal;

                if (vDb > v)
                    return (int)VersionCheck.DatabaseIsMoreNew;

                else 
                    return (int)VersionCheck.DatabaseIsOlder;
                
            }
            catch (SqlException sql_ex)
            {
                MessageBox.Show(sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
                return (int)VersionCheck.Failed;
            }
            catch (Exception system_ex)
            {
                MessageBox.Show(system_ex.Message.ToString());
                return (int)VersionCheck.Failed;
            }
        }

        public string[] ParseScriptToCommands(string strScript)
        {
            string[] commands;
            commands = Regex.Split(strScript, "GO\r\n", RegexOptions.IgnoreCase);
            return commands;
        }

     
        public static void SetDatabaseRights()
        {
            try
            {
                // Add an access control entry to the database file.
                ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename);
                ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename2);
            }
            catch (Exception e)
            {
                MessageBox.Show("Cannot set access rights for users to the database. Do you have the sufficient rights? Application will abort. Error: " + e.Message);
                Application.Exit();
                return;
            }
        }

        public static void StartSQLbrowserService()
        {
            System.ServiceProcess.ServiceController srvController = new System.ServiceProcess.ServiceController(SQL_BROWSER_SERVICE_NAME);
            try
            {
                // Check that the SQL browser service is not already running
                if (srvController.Status != System.ServiceProcess.ServiceControllerStatus.Running)
                {   // Service not running, start it.
                    srvController.Start();
                    srvController.WaitForStatus(System.ServiceProcess.ServiceControllerStatus.Running, TimeSpan.FromSeconds(50));
                }
            }

            catch (Exception e)
            {
                MessageBox.Show("Could not start the SQL Browser service (13078). Error:" + e.Message);
            }
        }

        // Configure the SQL browser service to autostart
        public static void AutostartSQLbrowserService()
        {
            try
            {
                //construct the management path
                string path = "Win32_Service.Name='" + SQL_BROWSER_SERVICE_NAME + "'";
                using (ManagementObject service = new ManagementObject(new ManagementPath(path)))
                {
                    object[] parameters = new object[1];
                    parameters[0] = "Automatic";
                    service.InvokeMethod("ChangeStartMode", parameters);

                }
            }
            catch
            {
                MessageBox.Show("Error, could not configure SQL Browser service (24888). Please check that SQL Server is intalled and that you are logged in with sufficient rights to configure services. Then retry the operation.");
            }
        }

        // Enable named pipes on the SQL Express server
        public static bool EnableNamedPipes()
        { 
            ManagementScope manScope = new ManagementScope(@"\\.\root\Microsoft\SqlServer\ComputerManagement");
            ManagementClass sqlServicesMan = new ManagementClass(manScope, new ManagementPath("SqlService"), null);
            ManagementClass serverProtocolsMan = new ManagementClass(manScope, new ManagementPath("ServerNetworkProtocol"), null);
            bool restarted = false; // Indicating if restart of SQL server was performed

            sqlServicesMan.Get();
            serverProtocolsMan.Get();

            foreach (ManagementObject prot in serverProtocolsMan.GetInstances())
            {
                prot.Get();
                if ((string)prot.GetPropertyValue("ProtocolName") == "Np" &&  //Named pipes
                    (string)prot.GetPropertyValue("InstanceName") == InstanceName)
                {   // We found the named pipes protocol
                    if (!(bool)prot.GetPropertyValue("Enabled"))
                    {   // Named pipes not activated
                        prot.InvokeMethod("SetEnable", null);  // Activate named pipes
              
                        // Check if user wants to restart SQL server
                        DialogResult resultRights;
                        resultRights = MessageBox.Show(string.Format("In order for users to use the AnywhereTS control panel, the {0} service on this computer need to be restarted. This operation might take up to 60 seconds. Do you want restart the {0} service now?", InstanceName), string.Format("AnywhereTS - Restart {0} (This operation might take up to 60 seconds!)", InstanceName), MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);
                        if (resultRights == DialogResult.Yes)
                        {
                            // Restart the SQL server
                            const uint sqlServerService = 1;
                            const uint sqlServiceStopped = 1;
                            foreach (ManagementObject svc in sqlServicesMan.GetInstances())
                            {
                                if ((uint)svc.GetPropertyValue("SqlServiceType") == sqlServerService &&
                                    (string)svc.GetPropertyValue("ServiceName") == string.Format("MSSQL${0}", InstanceName))
                                {
                                    svc.Get();
                                    if ((uint)svc.GetPropertyValue("State") != sqlServiceStopped)
                                    {
                                        svc.InvokeMethod("StopService", null);
                                    }
                                    svc.InvokeMethod("StartService", null);
                                    restarted = true;
                                }   // end if
                            }
                        }
                    } // end if 'named pipes protool'
                }
            } // foreach

            return restarted;
        }
    }
}