//
// 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.
//

//  .Net StockTrader Sample WCF Application for Benchmarking, Performance Analysis and Design Considerations for Service-Oriented Applications

//===============================================================================================
// Config is part of the SQLServer DAL for StockTrader.  This is called from the
// BSL to execute commands against the database.  It is constructed to use one SqlConnection per
// instance.  Hence, BSLs that use this DAL should always be instanced properly.
// The DAL will work with both ADO.NET and System.Transactions or ServiceComponents/Enterprise
// Services attributed transactions [autocomplete]. When using ADO.NET transactions,
// The BSL will control the transaction boundaries with calls to dal.BeginTransaction(); 
// dal.CommitTransaction(); dal.RollbackTransaction().
//===============================================================================================

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Trade.IDAL;
using Trade.Utility;
using Trade.ConfigServiceDataContract;

namespace Trade.DALSQLServer
{
    public class Config : IConfig
    {
        public Config()
        {
        }

        //Constructor for internal DAL-DAL calls to use an existing DB connection.
        public Config(SqlConnection conn)
        {
            _internalConnection = conn;
        }

        //_internalConnection: Used by a DAL instance such that a DAL instance,
        //associated with a BSL instance, will work off a single connection between BSL calls.
        private SqlConnection _internalConnection;

        //_internalADOTransaction: Used only when doing ADO.NET transactions.
        //This will be completely ignored when null, and not attached to a cmd object
        //In SQLServerHelper unless it has been initialized explicitly in the BSL with a
        //dal.BeginADOTransaction().  See app config setting in web.config and 
        //Trade.BusinessServiceHost.exe.config "Use System.Transactions Globally" which determines
        //whether user wants to run with ADO transactions or System.Transactions.  The DAL itself
        //is built to be completely agnostic and will work with either.
        private SqlTransaction _internalADOTransaction;

        //Used only when doing ADO.NET transactions.
        public void BeginADOTransaction()
        {
            if (_internalConnection.State != ConnectionState.Open)
                _internalConnection.Open();
            _internalADOTransaction = _internalConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        }

        //Used only when explicitly using ADO.NET transactions from the BSL.
        public void RollBackTransaction()
        {
            _internalADOTransaction.Rollback();
            _internalADOTransaction = null;
        }

        //Used only when explicitly using ADO.NET transactions from the BSL.
        public void CommitADOTransaction()
        {
            _internalADOTransaction.Commit();
            _internalADOTransaction = null;
        }

        public void Open(string connString)
        {
            if (_internalConnection == null)
                _internalConnection = new SqlConnection(connString);
            if (_internalConnection.State != ConnectionState.Open)
                _internalConnection.Open();
        }

        public void Close()
        {
            if (_internalConnection != null && _internalConnection.State != ConnectionState.Closed)
                _internalConnection.Close();
        }

        private const string PARM_CLIENT = "@Client";
        private const string PARM_BS = "@Bs";
        private const string PARM_OPS = "@Ops";
        private const string PARM_URL = "@Url";
        private const string PARM_SERVICENAME = "@ServiceName";
        private const string PARM_SEC = "@Sec";


        //SQL Queries to lookup configuration info for given services
        private const string SQL_GET_CLIENT_CONFIG = @"SET NOCOUNT ON; SELECT c.BS, s.URL, s.SEC FROM CLIENTTOBS as c INNER JOIN [SERVICE] AS s ON c.BS = s.SERVICENAME WHERE c.CLIENT = @Client";
        private const string SQL_GET_BS_CONFIG = @"SET NOCOUNT ON; SELECT b.OPS, b.URL, b.SEC, d.DBNAME, d.HOSTNAME, d.PORT FROM (SELECT 1 as Row, b1.OPS, s.URL, s.SEC	FROM BSTOOPS as b1 INNER JOIN [SERVICE] AS s ON b1.OPS = s.SERVICENAME WHERE b1.BS = @Bs) as b INNER JOIN (SELECT 1 as Row, DBNAME, HOSTNAME, PORT FROM DBCONFIG WHERE ACTIVE='Y') as d ON b.Row = d.Row";
        private const string SQL_GET_OPS_CONFIG = @"SET NOCOUNT ON; SELECT d.DBNAME, d.HOSTNAME, d.PORT FROM dbo.DBCONFIG as d WHERE ACTIVE='Y'";
        private const string SQL_FIND_CLIENT_TO_BS = @"SET NOCOUNT ON; SELECT b.CLIENT, b.BS FROM dbo.CLIENTTOBS as b WHERE b.CLIENT = @Client";
        private const string SQL_SET_CLIENT_TO_BS = @"INSERT INTO dbo.CLIENTTOBS (CLIENT, BS) VALUES (@Client, @Bs)";
        private const string SQL_UPDATE_CLIENT_TO_BS = @"UPDATE dbo.CLIENTTOBS WITH (ROWLOCK) SET BS=@Bs WHERE CLIENT=@Client";
        private const string SQL_FIND_BS_TO_OPS = @"SET NOCOUNT ON; SELECT b.BS, b.OPS FROM dbo.BSTOOPS as b WHERE b.BS = @Bs";
        private const string SQL_FIND_SERVICE = @"SET NOCOUNT ON; SELECT SERVICENAME, URL, SEC FROM dbo.SERVICE WHERE SERVICENAME = @ServiceName";
        private const string SQL_SET_BS_TO_OPS = @"INSERT INTO dbo.BSTOOPS (BS, OPS) VALUES (@Bs, @Ops)";
        private const string SQL_UPDATE_BS_TO_OPS = @"UPDATE dbo.BSTOOPS WITH (ROWLOCK) SET OPS=@Ops WHERE BS=@Bs";
        private const string SQL_COUNT_BS = @"SELECT COUNT(*) as count FROM dbo.SERVICE WHERE SERVICENAME LIKE '%_BS%'";
        private const string SQL_COUNT_OPS = @"SELECT COUNT(*) as count FROM dbo.SERVICE WHERE SERVICENAME LIKE '%_OPS%'";
        private const string SQL_GET_BS = @"SELECT SERVICENAME, URL, SEC FROM dbo.SERVICE WHERE SERVICENAME LIKE '%_BS%'";
        private const string SQL_GET_OPS = @"SELECT SERVICENAME, URL, SEC FROM dbo.SERVICE WHERE SERVICENAME LIKE '%_OPS%'";
        private const string SQL_UPDATE_SERVICE_LOCATION = @"UPDATE dbo.SERVICE WITH (ROWLOCK) SET URL = @Url, SEC = @Sec WHERE SERVICENAME = @ServiceName";
        private const string SQL_INSERT_SERVICE_LOCATION = @"INSERT INTO dbo.SERVICE (SERVICENAME, URL, SEC) VALUES (@ServiceName, @Url, @Sec)";

       /// <summary>
       /// Looks for a service location with the given ServiceName in the database. If found, 
       /// the URL and SEC values are updated. If not found, a new service location is inserted.
       /// </summary>
       /// <param name="location">Service Location consisting on a Service Name, URL and SEC value</param>
        public void SetServiceLocation(ServiceLocation location)
        {
            SqlParameter[] parm = new SqlParameter[] { new SqlParameter(PARM_SERVICENAME, SqlDbType.VarChar, 50) };
            parm[0].Value = location.ServiceName;
            
            SqlParameter[] parm2 = new SqlParameter[] { new SqlParameter(PARM_SERVICENAME, SqlDbType.VarChar, 50), new SqlParameter(PARM_URL, SqlDbType.VarChar, 500), new SqlParameter(PARM_SEC, SqlDbType.Bit) };
            parm2[0].Value = location.ServiceName;
            parm2[1].Value = location.ServiceURL;
            parm2[2].Value = location.Sec;

            SqlDataReader rdr = SQLServerHelper.ExecuteReaderSingleRow(_internalConnection, _internalADOTransaction, CommandType.Text, 
                SQL_FIND_SERVICE, parm);
            if (!rdr.Read())
            {
                rdr.Close();
                SQLServerHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, 
                    SQL_INSERT_SERVICE_LOCATION, parm2);
            }
            else
            {
                rdr.Close();
                SQLServerHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, 
                    SQL_UPDATE_SERVICE_LOCATION, parm2);
            }  
        }

        /// <summary>
        /// Looks up the given Client Service Name in the CLIENTTOBS table in the database 
        /// for information regarding configuration - of the Business Service and the DB
        /// </summary>
        /// <param name="bs">Name of the Order Processing Service</param>
        /// <returns>ClientConfigResponse object with config information on the BSuri and DB connection info. or null if the given ClientName is not in the database</returns>
        public ClientConfigResponse GetClientConfig(string client)
        {
            SqlParameter[] parm = new SqlParameter[] { new SqlParameter(PARM_CLIENT, SqlDbType.VarChar, 50) };
            parm[0].Value = client;
            SqlDataReader rdr = SQLServerHelper.ExecuteReaderSingleRow(_internalConnection, _internalADOTransaction, CommandType.Text,
                SQL_GET_CLIENT_CONFIG, parm);

            // Read and Guard
            if (!rdr.Read())
                return null;

            var response = new ClientConfigResponse();
            response.BS = rdr.GetString(rdr.GetOrdinal("URL"));
            response.BSName = rdr.GetString(rdr.GetOrdinal("BS"));
            response.Sec = rdr["SEC"] == DBNull.Value ? false : (bool)rdr["SEC"];

            rdr.Close();

            return response;
        }

        /// <summary>
        /// Looks up the given Business Service Name in the BSTOOPS table in the database 
        /// for information regarding configuration - of the Order Processing Service and the DB
        /// </summary>
        /// <param name="bs">Name of the Buiness Service</param>
        /// <returns>BSConfigResponse object with config information on the OPSuri and DB connection info, or null if the given BSName is not in the database</returns>
        public BSConfigResponse GetBSConfig(string bs)
        {
            SqlParameter[] parm = new SqlParameter[] { new SqlParameter(PARM_BS, SqlDbType.VarChar, 50) };
            parm[0].Value = bs;
            SqlDataReader rdr = SQLServerHelper.ExecuteReaderSingleRow(_internalConnection, _internalADOTransaction, CommandType.Text,
                SQL_GET_BS_CONFIG, parm);

            // Read and Gaurd
            if (!rdr.Read())
                return null;

            var response = new BSConfigResponse();
            response.OPS = rdr.GetString(rdr.GetOrdinal("URL"));
            response.OPSName = rdr.GetString(rdr.GetOrdinal("OPS"));
            response.Sec = rdr["SEC"] == DBNull.Value ? false : (bool)rdr["SEC"];
            response.DBName = rdr.GetString(rdr.GetOrdinal("DBNAME"));
            response.DBHostName = rdr.GetString(rdr.GetOrdinal("HOSTNAME"));
            response.DBPort = rdr.GetInt32(rdr.GetOrdinal("PORT"));

            rdr.Close();

            return response;
        }

        /// <summary>
        /// Finds the database configuration information from DBCONFIG for the Order Processing Service
        /// </summary>
        /// <param name="bs">Name of the Order Processing Service</param>
        /// <returns>OPSConfigResponse object with config information on the DB connection info</returns>
        public OPSConfigResponse GetOPSConfig(string ops)
        {
            SqlDataReader rdr = SQLServerHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text,
                SQL_GET_OPS_CONFIG);

            // Read and Gaurd
            if (!rdr.Read())
                return null;

            var response = new OPSConfigResponse();
            response.DBName = rdr.GetString(rdr.GetOrdinal("DBNAME"));
            response.DBHostName = rdr.GetString(rdr.GetOrdinal("HOSTNAME"));
            response.DBPort = rdr.GetInt32(rdr.GetOrdinal("PORT"));

            rdr.Close();

            return response;
        }

        /// <summary>
        /// Sets a connection between the specified client and business service
        /// If no ClientToBS configuration entry exists for this client, it is created
        /// or else the configuration for the client is replaced by this one
        /// </summary>
        public void SetClientToBS(ClientToBS clientConfig)
        {
            SqlParameter[] parm = new SqlParameter[] { new SqlParameter(PARM_CLIENT, SqlDbType.VarChar, 50) };
            parm[0].Value = clientConfig.Client;

            SqlParameter[] parm2 = new SqlParameter[] { new SqlParameter(PARM_CLIENT, SqlDbType.VarChar, 50), new SqlParameter(PARM_BS, SqlDbType.VarChar, 50) };
            parm2[0].Value = clientConfig.Client;
            parm2[1].Value = clientConfig.Bs;

            SqlDataReader rdr = SQLServerHelper.ExecuteReaderSingleRow(_internalConnection, _internalADOTransaction, CommandType.Text,
                SQL_FIND_CLIENT_TO_BS, parm);
            if (!rdr.Read())
            {
                rdr.Close();
                SQLServerHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_SET_CLIENT_TO_BS, parm2);
            }
            else
            {
                rdr.Close();
                SQLServerHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_UPDATE_CLIENT_TO_BS, parm2);
            }  
        }

        /// <summary>
        /// Sets a connection between the specified client and business service
        /// If no ClientToBS configuration entry exists for this client, it is created
        /// or else the configuration for the client is replaced by this one
        /// </summary>
        public void SetBSToOPS(BSToOPS bsConfig)
        {
            SqlParameter[] parm = new SqlParameter[] { new SqlParameter(PARM_BS, SqlDbType.VarChar, 50) };
            parm[0].Value = bsConfig.Bs;

            SqlParameter[] parm2 = new SqlParameter[] { new SqlParameter(PARM_BS, SqlDbType.VarChar, 50), new SqlParameter(PARM_OPS, SqlDbType.VarChar, 50) };
            parm2[0].Value = bsConfig.Bs;
            parm2[1].Value = bsConfig.Ops;

            SqlDataReader rdr = SQLServerHelper.ExecuteReaderSingleRow(_internalConnection, _internalADOTransaction, CommandType.Text,
                SQL_FIND_BS_TO_OPS, parm);
            if (!rdr.Read())
            {
                rdr.Close();
                SQLServerHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_SET_BS_TO_OPS, parm2);
            }
            else
            {
                rdr.Close();
                SQLServerHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_UPDATE_BS_TO_OPS, parm2);
            }
        }

        public ServiceLocation[] GetBSLocations()
        {
            int count = (int)SQLServerHelper.ExecuteScalarNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_COUNT_BS);
            ServiceLocation[] bsLocations = new ServiceLocation[count];
            SqlDataReader rdr = SQLServerHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_GET_BS);
            int i = 0;
            while (rdr.Read())
            {
                bsLocations[i] = new ServiceLocation();
                bsLocations[i].ServiceName = (string)rdr["SERVICENAME"];
                bsLocations[i].ServiceURL = (string)rdr["URL"];
                bsLocations[i].Sec = rdr["SEC"] == DBNull.Value ? false : (bool)rdr["SEC"];
                i++;
            }

            return bsLocations;
        }

        public ServiceLocation[] GetOPSLocations()
        {

            int count = (int)SQLServerHelper.ExecuteScalarNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_COUNT_OPS);
            ServiceLocation[] opsLocations = new ServiceLocation[count];
            SqlDataReader rdr = SQLServerHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_GET_OPS);
            
            int i = 0;
            while (rdr.Read())
            {
                opsLocations[i] = new ServiceLocation();
                opsLocations[i].ServiceName = (string)rdr["SERVICENAME"];
                opsLocations[i].ServiceURL = (string)rdr["URL"];
                opsLocations[i].Sec = rdr["SEC"] == DBNull.Value ? false : (bool)rdr["SEC"];
                i++;
            }

            return opsLocations;
        }

        private bool ConvertStringToBool(string value)
        {
            return value.Equals("Y", StringComparison.InvariantCultureIgnoreCase);
        }

    }
}