//******************************************************************************************************
//  SqlGenerator.cs - Gbtc
//
//  Copyright © 2012, Grid Protection Alliance.  All Rights Reserved.
//
//  Licensed to the Grid Protection Alliance (GPA) under one or more contributor license agreements. See
//  the NOTICE file distributed with this work for additional information regarding copyright ownership.
//  The GPA licenses this file to you under the MIT License (MIT), 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.opensource.org/licenses/MIT
//
//  Unless agreed to in writing, the subject software distributed under the License is distributed on an
//  "AS-IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. Refer to the
//  License for the specific language governing permissions and limitations.
//
//  Code Modification History:
//  ----------------------------------------------------------------------------------------------------
//  06/28/2010 - J. Ritchie Carroll
//       Generated original version of source code.
//  08/15/2008 - Mihir Brahmbhatt
//       Converted to C# extensions.
//  09/27/2010 - Mihir Brahmbhatt
//       Edited code comments.
//  12/20/2012 - Starlynn Danyelle Gilliam
//       Modified Header.
//
//******************************************************************************************************

using System;
using System.Collections;
using System.Data;
using System.Text;
using GSF.Data;

namespace DataMigrationUtility
{
    /// <summary>
    /// Common database functions
    /// </summary>
    public static class Common
    {
        /// <summary>
        /// Converts value to string, null objects (or DBNull objects) will return an empty string (""). 
        /// </summary>
        /// <param name="value">Value of <see cref="object"/> to convert to string.</param>
        /// <param name="nonNullValue"><see cref="string"/> to return if <paramref name="value"/> is null.</param>
        /// <returns><paramref name="value"/> as a string; if <paramref name="value"/> is null, <paramref name="nonNullValue"/> will be returned.</returns>
        public static string NotNull(object value, string nonNullValue = null)
        {
            if ((object)nonNullValue == null)
                return GSF.Common.ToNonNullString(value);

            return GSF.Common.ToNonNullString(value, nonNullValue);
        }
    }

    /// <summary>
    /// Generates a SQL statement for data processing.
    /// </summary>
    public class SQLGenerator
    {
        #region [ Members ]

        private Schema m_sourceSchema;
        private Table m_currentTable;
        private readonly ArrayList m_fieldList = new ArrayList();
        private string m_whereSQL;
        private bool m_includeNulls;

        #endregion

        #region [ Constructors ]

        public SQLGenerator()
        {
            m_whereSQL = "";
            m_includeNulls = false;
        }

        public SQLGenerator(Schema SourceSchema)
        {
            m_sourceSchema = SourceSchema;
        }

        #endregion

        #region [ Properties ]

        /// <summary>
        /// Get or set Source <see cref="Schema"/> object to use for SQL generation.
        /// </summary>
        public Schema SourceSchema
        {
            get
            {
                return m_sourceSchema;
            }
            set
            {
                m_sourceSchema = value;
            }
        }

        /// <summary>
        /// Get or set - WHERE clause SQL to append to generated SQL statements.
        /// </summary>
        public string WhereSQL
        {
            get
            {
                return m_whereSQL;
            }
            set
            {
                m_whereSQL = value;
            }
        }

        /// <summary>
        /// Get or set - to include NULL values in generated SQL statements.
        /// </summary>
        public bool IncludeNulls
        {
            get
            {
                return m_includeNulls;
            }
            set
            {
                m_includeNulls = value;
            }
        }


        /// <summary>
        /// Get <see cref="IDbConnection"/> setting for source <see cref="Schema"/>
        /// </summary>
        public IDbConnection Connection
        {
            get
            {
                return m_sourceSchema.Connection;
            }
        }

        /// <summary>
        /// Get or set source <see cref="Schema"/> table name.
        /// </summary>
        public string TableName
        {
            get
            {
                return m_currentTable.Name;
            }
            set
            {
                // Lookup table in collection
                m_currentTable = m_sourceSchema.Tables[value];

                if ((object)m_currentTable != null)
                {
                    // Clear any existing field values
                    // Field field = default(Field);
                    foreach (Field field in m_currentTable.Fields)
                    {
                        field.Value = null;
                    }

                    m_fieldList.Clear();
                }
                else
                {
                    throw new InvalidOperationException("Table '" + value + "' not found in schema");
                }
            }
        }

        #endregion

        #region [ Methods ]

        /// <summary>
        /// set value to current table column value
        /// </summary>
        /// <param name="FieldName">Set <paramref name="FieldValue"/> to <see cref="SourceSchema"/> table's field value</param>
        /// <param name="FieldValue">Set <see cref="object"/> value to <paramref name="FieldName"/> of current table</param>
        public void SetField(string FieldName, object FieldValue)
        {
            m_currentTable.Fields[FieldName].Value = FieldValue;

            if (m_fieldList.BinarySearch(FieldName, CaseInsensitiveComparer.Default) < 0)
            {
                m_fieldList.Add(FieldName);
                m_fieldList.Sort(CaseInsensitiveComparer.Default);
            }
        }

        /// <summary>
        /// Generate insert SQL statement
        /// </summary>
        /// <returns>insert SQL statement for current table to process</returns>
        public string InsertSQL()
        {
            StringBuilder insertSQL = new StringBuilder();
            string fieldName;
            bool setComma = false;

            insertSQL.Append("INSERT INTO ");
            insertSQL.Append(m_currentTable.SQLEscapedName);
            insertSQL.Append(" (");

            foreach (string currentFieldName in m_fieldList)
            {
                fieldName = currentFieldName;

                if (m_includeNulls || !Convert.IsDBNull(m_currentTable.Fields[fieldName].Value))
                {
                    if (setComma)
                        insertSQL.Append(", ");

                    insertSQL.Append(m_currentTable.Fields[fieldName].SQLEscapedName);
                    setComma = true;
                }
            }

            insertSQL.Append(") VALUES (");
            setComma = false;

            foreach (string currentFieldName in m_fieldList)
            {
                fieldName = currentFieldName;

                if (m_includeNulls || !Convert.IsDBNull(m_currentTable.Fields[fieldName].Value))
                {
                    if (setComma)
                        insertSQL.Append(", ");

                    insertSQL.Append(m_currentTable.Fields[fieldName].SQLEncodedValue);
                    setComma = true;
                }
            }

            insertSQL.Append(")");

            if (!setComma)
                throw new InvalidOperationException("No field values were specified in insert SQL");

            if (m_whereSQL.Length > 0)
            {
                insertSQL.Append(" ");
                insertSQL.Append(m_whereSQL);
            }

            return insertSQL.ToString();

        }

        /// <summary>
        /// Generate update SQL statement
        /// </summary>
        /// <returns>Update SQL statement to data processing</returns>
        public string UpdateSQL()
        {
            StringBuilder updateSQL = new StringBuilder();
            string fieldName;
            bool setComma = false;

            updateSQL.Append("UPDATE ");
            updateSQL.Append(m_currentTable.SQLEscapedName);
            updateSQL.Append(" SET ");

            foreach (string currentFieldName in m_fieldList)
            {
                fieldName = currentFieldName;

                if (m_includeNulls || !Convert.IsDBNull(m_currentTable.Fields[fieldName].Value))
                {
                    if (setComma)
                        updateSQL.Append(", ");

                    updateSQL.Append(m_currentTable.Fields[fieldName].SQLEscapedName);
                    updateSQL.Append(" = ");
                    updateSQL.Append(m_currentTable.Fields[fieldName].SQLEncodedValue);
                    setComma = true;
                }
            }

            if (!setComma)
                throw new InvalidOperationException("No field values were specified in update SQL");

            if (m_whereSQL.Length > 0)
            {
                updateSQL.Append(" ");
                updateSQL.Append(m_whereSQL);
            }

            return updateSQL.ToString();

        }

        #endregion
    }
}