//
// Licensed to the Apache Software Foundation (ASF) under one or more
// contributor license agreements.  See the NOTICE file distributed with
// this work for additional information regarding copyright ownership.
// The ASF licenses this file to You under the Apache License, Version 2.0
// (the "License"); you may not use this file except in compliance with
// the License.  You may obtain a copy of the License at
//
//      http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
//

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Collections.Generic;

namespace SetupActions
{
    /// <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>
    internal sealed class SetupAction 
    {
        // Connection Strings
        private readonly string CONNSTRING_SQLAUTH = @"server={0};Database={1};user id={2};password={3};Pooling=false;";
        private readonly string CONNSTRING_WINAUTH = @"server={0};Database={1};Integrated Security=SSPI;";

        // DB Create/Load Scripts
        private readonly string TRADEDB_CREATE_FILE = @"setup_utilities\DataLoad\createdb.sql";
        private readonly string TRADEDB_CREATE_DBCONFIG = @"setup_utilities\DataLoad\createdbconfig.sql";
        private readonly string TRADEDB_INSERT_DBCONFIG = @"setup_utilities\DataLoad\insertdbconfig.sql";

        // Xml Loading Stored Proc Scripts
        private readonly List<string> CREATE_PROC_SCRIPTS = new List<string>() { 
            @"setup_utilities\DataLoad\CreateInsertXMLAccountProfileProc.sql",
            @"setup_utilities\DataLoad\CreateInsertXMLAccountProc.sql",
            @"setup_utilities\DataLoad\CreateInsertXMLHoldingProc.sql",
            @"setup_utilities\DataLoad\CreateInsertXMLOrdersProc.sql",
            @"setup_utilities\DataLoad\CreateInsertXMLQuoteProc.sql"
        };
        
        // Xml Files to Load
        private readonly List<XmlLoadData> XML_LOAD_DATA = new List<XmlLoadData>() {
            new XmlLoadData { Filename=@"setup_utilities\DataLoad\StockTraderDB\Account.xml", StoredProcName="InsertAccountFromXML", Table="dbo.ACCOUNT" },
            new XmlLoadData { Filename=@"setup_utilities\DataLoad\StockTraderDB\AccountProfile.xml", StoredProcName="InsertAccountProfileFromXML" },
            new XmlLoadData { Filename=@"setup_utilities\DataLoad\StockTraderDB\Holding.xml", StoredProcName="InsertHoldingFromXML", Table="dbo.HOLDING" },
            new XmlLoadData { Filename=@"setup_utilities\DataLoad\StockTraderDB\Quote.xml", StoredProcName="InsertQuoteFromXML" },
            new XmlLoadData { Filename=@"setup_utilities\DataLoad\StockTraderDB\Orders.xml", StoredProcName="InsertOrdersFromXML", Table="dbo.ORDERS" }
        };

        // Error Messages
        private readonly string CREATE_LOGIN_FAILED_MSG = "Unable to login with the newly created trade user account.";

        // DB Creation Configuration
        private readonly string _dbServer;
        private readonly string _installPath;
        private readonly string _dbAdmin;
        private readonly string _dbPassword;
        private readonly string _authType;
        private readonly string _tradeUser = "trade";
        private readonly string _tradePassword = "yyy";
        private readonly string _tradeDb = "StockTraderDB";

        private string _connectionString = null;

        public SetupAction()
        {
            //Get values from config
            _dbServer = ConfigurationManager.AppSettings.Get("dbServer");
            _installPath = ConfigurationManager.AppSettings.Get("installPath");
            _dbAdmin = ConfigurationManager.AppSettings.Get("dbAdmin");
            _dbPassword = ConfigurationManager.AppSettings.Get("dbPassword");
            _authType = ConfigurationManager.AppSettings.Get("authType");
        }

        public void Install()
        {
            CreateDatabase(_tradeDb, _tradeUser, _tradePassword);
            CreateLoadXmlStoredProcs(_tradeDb);
            LoadStockTraderDB(_tradeDb);
            AddDBConfig(_tradeDb);
        }

        /// <summary>
        /// Gets a Connection string based on the authentication Type and supplied DB Name
        /// </summary>
        /// <param name="databaseName">The database name to connect</param>
        /// <returns>A SQL Server ConnectionString</returns>
        private string GetConnectionString(string databaseName)
        {
            return GetConnectionString(databaseName, _dbAdmin, _dbPassword);
        }

        /// <summary>
        /// Gets a Connection string based on the authentication Type and supplied DB Name
        /// </summary>
        /// <param name="databaseName">The database name to connect</param>
        /// <param name="username">The username to use when connecting to the database</param>
        /// <param name="password">The password for the supplied username</param>
        /// <returns>A SQL Server ConnectionString</returns>
        private string GetConnectionString(string databaseName, string username, string password)
        {
            if (_authType != null && _authType.Equals("Integrated", StringComparison.InvariantCultureIgnoreCase))
                return string.Format(CONNSTRING_WINAUTH, new object[] { _dbServer, databaseName });

            return string.Format(CONNSTRING_SQLAUTH, new object[] { _dbServer, databaseName, username, password });
        }

        /// <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>
        private void CreateDatabase(string databaseName, string userid, string password)
        {
            _connectionString = GetConnectionString("master");
            SqlConnection sqlConnection = new SqlConnection(_connectionString);
            try
            {
                // Connect to master
                sqlConnection.Open();

                //Drop DB
                string dropDB = "IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'" + databaseName + "') DROP DATABASE [" + databaseName + "]";
                SqlCommand sqlCommand = new SqlCommand(dropDB, sqlConnection);
                sqlCommand.ExecuteNonQuery();

                //Create DB
                string createDB = "CREATE DATABASE [" + databaseName + "];";
                sqlCommand.CommandText = createDB;
                sqlCommand.ExecuteNonQuery();
                Console.Write(".");

                //Config DB
                string configDB = "IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) EXEC [" + databaseName + "].[dbo].[sp_fulltext_database] @action = 'enable';" +
                                         "ALTER DATABASE [" + databaseName + "] SET ANSI_NULL_DEFAULT OFF;" +
                                         "ALTER DATABASE [" + databaseName + "] SET ANSI_NULLS OFF;" +
                                         "ALTER DATABASE [" + databaseName + "] SET ANSI_PADDING OFF;" +
                                         "ALTER DATABASE [" + databaseName + "] SET ANSI_WARNINGS OFF;" +
                                         "ALTER DATABASE [" + databaseName + "] SET ARITHABORT OFF;" +
                                         "ALTER DATABASE [" + databaseName + "] SET AUTO_CLOSE OFF;" +
                                         "ALTER DATABASE [" + databaseName + "] SET AUTO_CREATE_STATISTICS ON;" +
                                         "ALTER DATABASE [" + databaseName + "] SET AUTO_SHRINK OFF;" +
                                         "ALTER DATABASE [" + databaseName + "] SET AUTO_UPDATE_STATISTICS ON;" +
                                         "ALTER DATABASE [" + databaseName + "] SET CURSOR_CLOSE_ON_COMMIT OFF;" +
                                         "ALTER DATABASE [" + databaseName + "] SET CURSOR_DEFAULT  GLOBAL;" +
                                         "ALTER DATABASE [" + databaseName + "] SET CONCAT_NULL_YIELDS_NULL OFF;" +
                                         "ALTER DATABASE [" + databaseName + "] SET NUMERIC_ROUNDABORT OFF;" +
                                         "ALTER DATABASE [" + databaseName + "] SET QUOTED_IDENTIFIER OFF;" +
                                         "ALTER DATABASE [" + databaseName + "] SET RECURSIVE_TRIGGERS OFF;" +
                                         "ALTER DATABASE [" + databaseName + "] SET  ENABLE_BROKER;" +
                                         "ALTER DATABASE [" + databaseName + "] SET AUTO_UPDATE_STATISTICS_ASYNC OFF;" +
                                         "ALTER DATABASE [" + databaseName + "] SET DATE_CORRELATION_OPTIMIZATION OFF;" +
                                         "ALTER DATABASE [" + databaseName + "] SET TRUSTWORTHY OFF;" +
                                         "ALTER DATABASE [" + databaseName + "] SET ALLOW_SNAPSHOT_ISOLATION OFF;" +
                                         "ALTER DATABASE [" + databaseName + "] SET PARAMETERIZATION SIMPLE;" +
                                         "ALTER DATABASE [" + databaseName + "] SET  READ_WRITE;" +
                                         "ALTER DATABASE [" + databaseName + "] SET RECOVERY FULL;" +
                                         "ALTER DATABASE [" + databaseName + "] SET  MULTI_USER;" +
                                         "ALTER DATABASE [" + databaseName + "] SET PAGE_VERIFY CHECKSUM;" +
                                         "ALTER DATABASE [" + databaseName + "] SET DB_CHAINING OFF;";

                sqlCommand.CommandText = configDB;
                sqlCommand.ExecuteNonQuery();
                Console.Write(".");

                // Create Login
                string createLogin = "IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'" + userid + "') " +
                                         "CREATE LOGIN [" + userid + "] WITH PASSWORD='" + password + "', DEFAULT_DATABASE=" +
                                         "[" + databaseName + "], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;";

                sqlCommand.CommandText = createLogin;
                sqlCommand.ExecuteNonQuery();
                Console.Write(".");

                // Logout of Master
                sqlConnection.Close();

                // Login to the databaseName using the Master Account
                _connectionString = GetConnectionString(databaseName);
                sqlConnection = new SqlConnection(_connectionString);
                sqlConnection.Open();

                // Create User
                string createUser = "IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'" + userid + "') CREATE USER" +
                                             "[" + userid + "] FOR LOGIN [" + userid + "] WITH DEFAULT_SCHEMA=[dbo];";

                sqlCommand = new SqlCommand(createUser, sqlConnection);
                sqlCommand.ExecuteNonQuery();
                Console.Write(".");

                // Add Role
                string addRole = "EXEC sp_addrolemember 'db_owner', '" + userid + "'";
                sqlCommand.CommandText = addRole;
                sqlCommand.ExecuteNonQuery();
                Console.Write(".");

                // Logout of databaseName with Master credentials
                sqlConnection.Close();

                // Login as newly created user.  
                // Don't need to but rather test connectivity here right away to make sure 
                // the DB is in good state for the new trade user.
                _connectionString = GetConnectionString(databaseName, userid, password);
                sqlConnection = new SqlConnection(_connectionString);
                try
                {
                    sqlConnection.Open();
                }
                catch (Exception e)
                {
                    throw new Exception(CREATE_LOGIN_FAILED_MSG + "  Error: " + e.Message);
                }

                ///Create Schema for DB
                string createCommand = ReadTextFile(Path.Combine(_installPath, TRADEDB_CREATE_FILE));
                sqlCommand = new SqlCommand(createCommand, sqlConnection);
                sqlCommand.ExecuteNonQuery();
                Console.Write(".");
            }
            finally
            {
                // Close the connection
                if (sqlConnection.State == ConnectionState.Open)
                    sqlConnection.Close();
                sqlConnection.Dispose();

                // Clear all pools so we don't get any master connections going forward
                SqlConnection.ClearAllPools();
            }
        }

        /// <summary>
        /// Similar to createLoadXmlRepositoryProcs, except for StockTraderDB tables.
        /// </summary>
        /// 
        /// <param name="databaseName"></param>
        private void CreateLoadXmlStoredProcs(string databaseName)
        {
            _connectionString = GetConnectionString(databaseName);
            SqlConnection sqlConnection = new SqlConnection(_connectionString);
            try
            {
                sqlConnection.Open();

                foreach (string file in CREATE_PROC_SCRIPTS)
                {
                    // Create Store
                    string createCommand = ReadTextFile(Path.Combine(_installPath, file));
                    SqlCommand sqlCommand = new SqlCommand(createCommand, sqlConnection);
                    sqlCommand.ExecuteNonQuery();
                    Console.Write(".");
                }
            }
            finally
            {
                // Close the connection
                if (sqlConnection.State == ConnectionState.Open)
                    sqlConnection.Close();
                sqlConnection.Dispose();
            }
        }
       
        /// <summary>
        /// Loads the StockTraderDB tables with initial data from XML files.
        /// </summary>
        /// 
        /// <param name="repository"></param>
        private void LoadStockTraderDB(string repository)
        {
            _connectionString = GetConnectionString(repository);
            SqlConnection sqlConnection = new SqlConnection(_connectionString);
            try
            {
                sqlConnection.Open();

                foreach (var xmlData in XML_LOAD_DATA)
                {
                    SqlCommand identityCommand;
                    if (xmlData.Table != null)
                    {
                        // Need to turn off identities first for certain tables.
                        identityCommand = new SqlCommand("SET IDENTITY_INSERT " + xmlData.Table + " ON", sqlConnection);
                        identityCommand.ExecuteNonQuery();
                        Console.Write(".");
                    }

                    // Create the sql parameter with the xml data from the file
                    SqlParameter xmlParameter = new SqlParameter("sXML", SqlDbType.NText, 60000000);
                    xmlParameter.Value = ReadTextFile(Path.Combine(_installPath, xmlData.Filename));

                    // execute the load xml Stored Procedure
                    SqlCommand insertProcCommand = new SqlCommand(xmlData.StoredProcName, sqlConnection);
                    insertProcCommand.CommandType = CommandType.StoredProcedure;
                    insertProcCommand.Parameters.Add(xmlParameter);
                    insertProcCommand.ExecuteNonQuery();
                    Console.Write(".");

                    if (xmlData.Table != null)
                    {
                        // reset the identities for certain tables
                        identityCommand = new SqlCommand("SET IDENTITY_INSERT " + xmlData.Table + " OFF", sqlConnection);
                        identityCommand.ExecuteNonQuery();
                        Console.Write(".");
                    }
                }
            }
            finally
            {
                // Close the connection
                if (sqlConnection.State == ConnectionState.Open)
                    sqlConnection.Close();
                sqlConnection.Dispose();
            }
        }

        /// <summary>
        /// Creates the tables needed for the database configuration system
        /// i.e. SERVICE, CLIENTTOBS, BSTOOPS, DBCONFIG
        /// </summary>
        /// <param name="installPath"></param>
        /// <param name="databaseName"></param>
        private void AddDBConfig(string databaseName)
        {
            _connectionString = GetConnectionString(databaseName);
            SqlConnection sqlConnection = new SqlConnection(_connectionString);
            try
            {
                sqlConnection.Open();

                //creates the necessary tables: CLIENTTOBS, BSTOOPS, SERVICE, DBCONFIG
                string createCommand = ReadTextFile(Path.Combine(_installPath, TRADEDB_CREATE_DBCONFIG));
                SqlCommand sqlCommand = new SqlCommand(createCommand, sqlConnection);
                sqlCommand.ExecuteNonQuery();
                Console.Write(".");

                //inserts the config info into these tables
                createCommand = ReadTextFile(Path.Combine(_installPath, TRADEDB_INSERT_DBCONFIG));
                sqlCommand = new SqlCommand(createCommand, sqlConnection);
                sqlCommand.ExecuteNonQuery();
                Console.Write(".");
            }
            finally
            {
                // Close the connection
                if (sqlConnection.State == ConnectionState.Open)
                    sqlConnection.Close();
                sqlConnection.Dispose();
            }
        }

        /// <summary>
        /// Use a StreamReader to return the string value of a text document.
        /// </summary>
        /// <param name="filename">The text file to load</param>
        /// <returns>A string of the contents in the text file</returns>
        private static string ReadTextFile(string filename)
        {
            string fileContents = string.Empty;
            using (StreamReader file = new StreamReader(filename))
            {
                fileContents = file.ReadToEnd();
                file.Close();
            }
            return fileContents;
        }
    }
}