/*==========================================================================================================================
        | METHOD: LOAD
        >---------------------------------------------------------------------------------------------------------------------------
        | ### NOTE JJC081115: This method should be broken down into private helper functions to better separate functionality,
        | avoid lengthy nested blocks, and enhance code readability.
        \-------------------------------------------------------------------------------------------------------------------------*/
        /// <summary>
        ///   Interface method that loads topics into memory.
        /// </summary>
        /// <param name="topicKey">The string identifier for the topic.</param>
        /// <param name="topicId">The integer identifier for the topic.</param>
        /// <param name="depth">The level to which to recurse through and load a topic's children.</param>
        /// <param name="version">The DateTime stamp signifying when the topic was saved.</param>
        /// <returns>A topic object.</returns>
        /// <exception cref="Exception">
        ///   The topic Ignia.Topics.<c>contentType</c> does not derive from Ignia.Topics.Topic.
        /// </exception>
        /// <exception cref="Exception">
        ///   Topics failed to load: <c>ex.Message</c>
        /// </exception>
        internal override Topic Load(string topicKey, int topicId, int depth, DateTime? version = null)
        {
            /*------------------------------------------------------------------------------------------------------------------------
              | Validate contracts
              \-----------------------------------------------------------------------------------------------------------------------*/
              Contract.Ensures(Contract.Result<Topic>() != null);
              ValidateConnectionStrings("TopicsServer");

              /*------------------------------------------------------------------------------------------------------------------------
              | Establish database connection
              \-----------------------------------------------------------------------------------------------------------------------*/
              Dictionary<int, Topic>    topics          = new Dictionary<int, Topic>();
              SqlConnection             connection      = new SqlConnection(ConfigurationManager.ConnectionStrings?["TopicsServer"]?.ConnectionString);
              SqlCommand                command         = new SqlCommand("topics_GetTopics", connection);
              command.CommandType                       = CommandType.StoredProcedure;
              SqlDataReader             reader          = null;
              int                       sortOrder       = 0;

              try {

            /*----------------------------------------------------------------------------------------------------------------------
            | Open connection
            \---------------------------------------------------------------------------------------------------------------------*/
            connection.Open();

            /*----------------------------------------------------------------------------------------------------------------------
            | Establish query parameters
            \---------------------------------------------------------------------------------------------------------------------*/
            AddSqlParameter(command, "TopicName",   topicKey,                                       SqlDbType.VarChar);
            AddSqlParameter(command, "Depth",       depth.ToString(CultureInfo.InvariantCulture),   SqlDbType.Int);
            AddSqlParameter(command, "TopicID",     topicId.ToString(CultureInfo.InvariantCulture), SqlDbType.Int);

            if (version != null) {
              AddSqlParameter(command, "Version",   version.ToString(),                             SqlDbType.DateTime);
            }

            /*----------------------------------------------------------------------------------------------------------------------
            | Execute query/reader
            \---------------------------------------------------------------------------------------------------------------------*/
            reader = command.ExecuteReader();

            /*----------------------------------------------------------------------------------------------------------------------
            | Populate topics
            \---------------------------------------------------------------------------------------------------------------------*/
            while (reader.Read()) {
              AddTopic(reader, topics, out sortOrder);
            }

            /*----------------------------------------------------------------------------------------------------------------------
            | Read attributes
            \---------------------------------------------------------------------------------------------------------------------*/

            // Move to TopicAttributes dataset
            reader.NextResult();

            while (reader.Read()) {
              SetIndexedAttributes(reader, topics);
            }

            /*----------------------------------------------------------------------------------------------------------------------
            | Read blob
            \---------------------------------------------------------------------------------------------------------------------*/

            // Move to blob dataset
            reader.NextResult();

            // Loop through each blob, each record associated with a specific record
            while (reader.Read()) {
              SetBlobAttributes(reader, topics);
            }

            /*----------------------------------------------------------------------------------------------------------------------
            | Read related items
            \---------------------------------------------------------------------------------------------------------------------*/

            // Move to the relationships dataset
            reader.NextResult();

            // Loop through each relationship; multiple records may exist per topic
            while (reader.Read()) {
              SetRelationships(reader, topics);
            }

            /*----------------------------------------------------------------------------------------------------------------------
            | Read version history
            \---------------------------------------------------------------------------------------------------------------------*/

            // Move to the version history dataset
            reader.NextResult();

            // Loop through each version; multiple records may exist per topic
            while (reader.Read()) {
              SetVersionHistory(reader, topics);
            }

              }

              /*------------------------------------------------------------------------------------------------------------------------
              | Catch exception
              \-----------------------------------------------------------------------------------------------------------------------*/
              catch (Exception ex) {
            throw new Exception("Topics failed to load: " + ex.Message);
              }

              /*------------------------------------------------------------------------------------------------------------------------
              | Close connection
              \-----------------------------------------------------------------------------------------------------------------------*/
              finally {
            reader?.Dispose();
            command?.Dispose();
            connection.Close();
              }

              /*------------------------------------------------------------------------------------------------------------------------
              | Return objects
              \-----------------------------------------------------------------------------------------------------------------------*/
              if (topics.Count == 0) return null;
              return topics[topics.Keys.ElementAt(0)];
        }