using System;
using System.DirectoryServices;
using System.Data;
using System.ComponentModel;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.Threading;
using System.Diagnostics;
using System.IO;
using System.Reflection;
using System.Configuration;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;



namespace SetupAction
{
    /// <summary>
    /// This is a custom setup action that overrides Installer base and is called after setup copies filed, automatically
    /// by the setup program. It will create IIS virtual directories based on WMI, register script maps, and create the databases.
    /// </summary>
    public partial class SetupAction 
    {
        

        private string SQL_CONN = null;
        public static readonly string CONNSTRING_SQLAUTH = "server={0};Database={1};user id={2};password={3};Pooling=false;";
        //newstrings 
        string CREATE_LOADACCOUNTPROFILEXML = @"Setup\DATALOAD\CreateInsertXMLAccountProfileProc.sql";
        string CREATE_LOADACCOUNTXML = @"Setup\DATALOAD\CreateInsertXMLAccountProc.sql";
        string CREATE_LOADHOLDINGXML = @"Setup\DATALOAD\CreateInsertXMLHoldingProc.sql";
        string CREATE_LOADORDERSXML = @"Setup\DATALOAD\CreateInsertXMLOrdersProc.sql";
        string CREATE_LOADQUOTEXML = @"Setup\DATALOAD\CreateInsertXMLQuoteProc.sql";
        string CONNSTRING_WINAUTH = @"EMPTY";
        string MSG_CHECK_SQL_AUTH = string.Empty;
        
        public static string dbServer = null;
        private string installPath = null;
        //private string createDBs = null;
        public string dbAdmin = null;
        public string dbPassword = null;
        public string authType = null;
        public static readonly string tradeuser = "trade";
        public static readonly string tradepassword = "trade";
        public static readonly string TRADEDB = "StockTraderDB";
        public const string TRADEDB_CREATE_FILE = "createdb.sql";

        //public void Install(System.Collections.IDictionary stateSaver)
        public void Install()
        {

            //Get values from config
            dbServer = ConfigurationManager.AppSettings.Get("dbServer");
            installPath = ConfigurationManager.AppSettings.Get("installPath");
            //not used createDBs = ConfigurationManager.AppSettings.Get("createDBs");
            dbAdmin = ConfigurationManager.AppSettings.Get("dbAdmin");
            dbPassword = ConfigurationManager.AppSettings.Get("dbPassword");
            authType = ConfigurationManager.AppSettings.Get("authType");

            //string CREATE_LOADACCOUNTPROFILEXML = @"Setup\DATALOAD\" + createfilename;
            //string CREATE_LOADACCOUNTXML = string.Empty;
            //string CREATE_LOADHOLDINGXML = string.Empty;
            //string CREATE_LOADORDERSXML = string.Empty;
            //string CREATE_LOADQUOTEXML = string.Empty;

            try
            {
                createDatabase(installPath, TRADEDB_CREATE_FILE, TRADEDB, tradeuser, tradepassword);
                createLoadXmlStockTraderDBProcs(installPath, TRADEDB, dbAdmin, dbPassword);
                loadStockTraderDB(installPath, TRADEDB);
            }
            catch (Exception e)
            {
                throw new Exception("MSG_DBFAIL " + e.Message);
            }
        }
        
        /// <summary>
        /// Similar to createLoadXmlRepositoryProcs, except for StockTraderDB tables.
        /// </summary>
        /// <param name="installPath"></param>
        /// <param name="databaseName"></param>
        /// <param name="userid"></param>
        /// <param name="password"></param>
        public void createLoadXmlStockTraderDBProcs(string installPath, string databaseName, string userid, string password)
        {
            SQL_CONN = String.Format(SetupAction.CONNSTRING_SQLAUTH, new object[] { dbServer, databaseName, userid, password });
            SqlConnection sqlConnection = new SqlConnection(SQL_CONN);
            sqlConnection.Open();

            ///ACCOUNT_PROFILE_LOAD_XML
            System.IO.StreamReader file = new System.IO.StreamReader(installPath + CREATE_LOADACCOUNTPROFILEXML);
            string createCommand = file.ReadToEnd();
            file.Close();
            SqlCommand sqlCommand = new SqlCommand(createCommand, sqlConnection);
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.ExecuteNonQuery();

            ///ACCOUNT_LOAD_XML
            file = new System.IO.StreamReader(installPath + CREATE_LOADACCOUNTXML);
            createCommand = file.ReadToEnd();
            file.Close();
            sqlCommand = new SqlCommand(createCommand, sqlConnection);
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.ExecuteNonQuery();

            ///HOLDING_LOAD_XML
            file = new System.IO.StreamReader(installPath + CREATE_LOADHOLDINGXML);
            createCommand = file.ReadToEnd();
            file.Close();
            sqlCommand = new SqlCommand(createCommand, sqlConnection);
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.ExecuteNonQuery();

            ///ORDERS_LOAD_XML
            file = new System.IO.StreamReader(installPath + CREATE_LOADORDERSXML);
            createCommand = file.ReadToEnd();
            file.Close();
            sqlCommand = new SqlCommand(createCommand, sqlConnection);
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.ExecuteNonQuery();

            ///QUOTE_LOAD_XML
            file = new System.IO.StreamReader(installPath + CREATE_LOADQUOTEXML);
            createCommand = file.ReadToEnd();
            file.Close();
            sqlCommand = new SqlCommand(createCommand, sqlConnection);
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.ExecuteNonQuery();

            sqlConnection.Close();
        }

        /// <summary>
        /// Creates the initial DB schemas for all StockTrader databases.
        /// </summary>
        /// <param name="installPath"></param>
        /// <param name="createfilename"></param>
        /// <param name="repositoryName"></param>
        /// <param name="userid"></param>
        /// <param name="password"></param>
        public void createDatabase(string installPath, string createfilename, string repositoryName, string userid, string password)
        {
            string configDB = "IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) EXEC [" + repositoryName + "].[dbo].[sp_fulltext_database] @action = 'enable';" +
                                         "ALTER DATABASE [" + repositoryName + "] SET ANSI_NULL_DEFAULT OFF;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET ANSI_NULLS OFF;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET ANSI_PADDING OFF;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET ANSI_WARNINGS OFF;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET ARITHABORT OFF;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET AUTO_CLOSE OFF;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET AUTO_CREATE_STATISTICS ON;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET AUTO_SHRINK OFF;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET AUTO_UPDATE_STATISTICS ON;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET CURSOR_CLOSE_ON_COMMIT OFF;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET CURSOR_DEFAULT  GLOBAL;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET CONCAT_NULL_YIELDS_NULL OFF;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET NUMERIC_ROUNDABORT OFF;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET QUOTED_IDENTIFIER OFF;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET RECURSIVE_TRIGGERS OFF;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET  ENABLE_BROKER;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET AUTO_UPDATE_STATISTICS_ASYNC OFF;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET DATE_CORRELATION_OPTIMIZATION OFF;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET TRUSTWORTHY OFF;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET ALLOW_SNAPSHOT_ISOLATION OFF;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET PARAMETERIZATION SIMPLE;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET  READ_WRITE;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET RECOVERY FULL;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET  MULTI_USER;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET PAGE_VERIFY CHECKSUM;" +
                                         "ALTER DATABASE [" + repositoryName + "] SET DB_CHAINING OFF;";

            string dropDB = null;
            if (authType.Equals("Integrated"))
                SQL_CONN = string.Format(CONNSTRING_WINAUTH, new object[] { dbServer, "master", dbAdmin, dbPassword });
            else
                SQL_CONN = string.Format(CONNSTRING_SQLAUTH, new object[] { dbServer, "master", dbAdmin, dbPassword });
            SqlConnection sqlConnection = new SqlConnection(SQL_CONN);
            try
            {
                sqlConnection.Open();
                dropDB = "IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'" + repositoryName + "') DROP DATABASE [" + repositoryName + "]";
                SqlCommand sqlCommand = new SqlCommand(dropDB, sqlConnection);
                sqlCommand.CommandTimeout = 1200;
                sqlCommand.CommandType = CommandType.Text;

                //Drop DB
                sqlCommand.ExecuteNonQuery();

                //Create DB
                string strCreateDB = "CREATE DATABASE [" + repositoryName + "];";
                sqlCommand.CommandText = strCreateDB;
                sqlCommand.ExecuteNonQuery();

                //Config DB
                sqlCommand.CommandText = configDB;
                sqlCommand.ExecuteNonQuery();

                string createlogin = "IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'" + userid + "') " +
                                         "CREATE LOGIN [" + userid + "] WITH PASSWORD='" + password + "', DEFAULT_DATABASE=" +
                                         "[" + repositoryName + "], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;";
                string createuser = "IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'" + userid + "') CREATE USER" +
                                             "[" + userid + "] FOR LOGIN [" + userid + "] WITH DEFAULT_SCHEMA=[dbo];";

                //create login
                sqlCommand.CommandText = createlogin;
                sqlCommand.ExecuteNonQuery();

                //Logout of Master

                sqlConnection.Close();

                //Now create user for new DB by logging in to new DB with admin rights

                if (authType.Equals("Integrated"))
                    SQL_CONN = string.Format(CONNSTRING_WINAUTH, new object[] { dbServer, repositoryName, dbAdmin, dbPassword });
                else
                    SQL_CONN = string.Format(CONNSTRING_SQLAUTH, new object[] { dbServer, repositoryName, dbAdmin, dbPassword });
                sqlConnection = new SqlConnection(SQL_CONN);
                Server server = new Server(new ServerConnection(sqlConnection));
                server.ConnectionContext.ExecuteNonQuery(createuser);
                //Logout of Trade DB
                sqlConnection.Close();
                System.Data.SqlClient.SqlConnection.ClearAllPools();

                //Login to Master and use SMO to create role mapping of DBO
                if (authType.Equals("Integrated"))
                    SQL_CONN = string.Format(CONNSTRING_WINAUTH, new object[] { dbServer, repositoryName, dbAdmin, dbPassword });
                else
                    SQL_CONN = string.Format(CONNSTRING_SQLAUTH, new object[] { dbServer, repositoryName, dbAdmin, dbPassword });
                sqlConnection = new SqlConnection(SQL_CONN);
                //new SMO functionality
                server = new Server(new ServerConnection(sqlConnection));
                server.ConnectionContext.ExecuteNonQuery("exec sp_addrolemember 'db_owner','" + userid + "'");

                //Logout
                sqlConnection.Close();

                //Login as newly created user.  Don't need to but rather test connectivity here right away to make sure DB in good state for new trade user.
                SQL_CONN = "server=" + dbServer + ";Database=" + repositoryName + ";user id=" + userid + ";password=" + password;
                sqlConnection = new SqlConnection(SQL_CONN);
                try
                {
                    sqlConnection.Open();
                }
                catch (Exception e)
                {
                    throw new Exception(MSG_CHECK_SQL_AUTH + ". Exc: " + e.Message);
                }

                ///Create Schema for DB
                System.IO.StreamReader file = new System.IO.StreamReader(installPath + @"Setup\DATALOAD\" + createfilename);
                string createCommand = file.ReadToEnd();
                file.Close();
                sqlCommand = new SqlCommand(createCommand, sqlConnection);
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.ExecuteNonQuery();
                sqlConnection.Close();
                return;
            }
            catch (Exception e)
            {
                if (sqlConnection.State == ConnectionState.Open)
                    sqlConnection.Close();
                System.Data.SqlClient.SqlConnection.ClearAllPools();
                sqlConnection.Dispose();
                throw new Exception(e.Message);
            }
        }

       
        /// <summary>
        /// Loads the StockTraderDB tables with initial data from XML files.
        /// </summary>
        /// <param name="installPath"></param>
        /// <param name="repository"></param>
        public void loadStockTraderDB(string installPath, string repository)
        {
            if (authType.Equals("Integrated"))
                SQL_CONN = String.Format(CONNSTRING_WINAUTH, new object[] { dbServer, repository, dbAdmin, dbPassword });
            else
                SQL_CONN = String.Format(SetupAction.CONNSTRING_SQLAUTH, new object[] { dbServer, repository, dbAdmin, dbPassword });
            string xmlFile = null;
            string sql = null;
            string table = null;
            for (int i = 0; i < 5; i++)
            {
                switch (i)
                {
                    case 0:
                        {
                            xmlFile = installPath + "Setup\\DATALOAD\\" + repository + "\\Account.xml";
                            sql = "InsertAccountFromXML";
                            table = "dbo.ACCOUNT";
                            break;
                        }
                    case 1:
                        {
                            xmlFile = installPath + "Setup\\DATALOAD\\" + repository + "\\AccountProfile.xml";
                            sql = "InsertAccountProfileFromXML";
                            table = null;
                            break;
                        }
                    case 2:
                        {
                            xmlFile = installPath + "Setup\\DATALOAD\\" + repository + "\\Quote.xml";
                            sql = "InsertQuoteFromXML";
                            table = null;
                            break;
                        }
                    case 3:
                        {
                            xmlFile = installPath + "Setup\\DATALOAD\\" + repository + "\\Holding.xml";
                            sql = "InsertHoldingFromXML";
                            table = "dbo.HOLDING";
                            break;
                        }
                    case 4:
                        {
                            xmlFile = installPath + "Setup\\DATALOAD\\" + repository + "\\Orders.xml";
                            sql = "InsertOrdersFromXML";
                            table = "dbo.ORDERS";
                            break;
                        }
                }
                SqlConnection conn = new SqlConnection(SQL_CONN);
                conn.Open();
                string configContent = null;
                if (table != null)
                {
                    //Need to turn off identities first for certain tables.
                    SqlCommand cmd = new SqlCommand("SET IDENTITY_INSERT " + table + " ON");
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                }
                using (StreamReader sr = new StreamReader(xmlFile))
                {
                    configContent = sr.ReadToEnd();
                }
                SqlParameter parmDoc = new SqlParameter("sXML", SqlDbType.NText, 60000000);
                parmDoc.Value = configContent;
                SqlCommand cmd2 = new SqlCommand(sql);
                cmd2.CommandType = CommandType.StoredProcedure;
                cmd2.Connection = conn;
                cmd2.Parameters.Add(parmDoc);
                try
                {
                    cmd2.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }
                if (table != null)
                {
                    cmd2 = new SqlCommand("SET IDENTITY_INSERT " + table + " OFF");
                    cmd2.Connection = conn;
                    cmd2.CommandType = CommandType.Text;
                    cmd2.ExecuteNonQuery();
                }
                conn.Close();
            }
        }
    }
}