//******************************************************************************************************
//  DataDeleter.cs - Gbtc
//
//  Copyright © 2018, 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://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:
//  ----------------------------------------------------------------------------------------------------
//  11/08/2018 - Stephen Wills
//       Generated original version of source code from code written in 2003.
//
//******************************************************************************************************

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;

namespace GSF.Data
{
    //  Note: user must define primary key fields in the database or through code to be used for deletes from each table
    //  in the table collection in order for the deletes to occur, hence any tables which have no key fields defined
    //  yet appear in the table collection will not have their records removed...

    /// <summary>
    /// This class defines a common set of functionality that any data operation implementation can use 
    /// </summary>
    public class DataDeleter : BulkDataOperationBase
    {
        #region [ Members ]

        // Events

        /// <summary>
        /// Disposed event.
        /// </summary>
        public event EventHandler Disposed;

        #endregion

        #region [ Constructors ]

        /// <summary>
        /// Creates a new <see cref="DataDeleter"/>.
        /// </summary>
        public DataDeleter()
        {
        }

        /// <summary>
        /// Creates a new <see cref="DataDeleter"/>.
        /// </summary>
        public DataDeleter(string fromConnectString, string toConnectString)
            : base(fromConnectString, toConnectString)
        {
        }

        /// <summary>
        /// Creates a new <see cref="DataDeleter"/>.
        /// </summary>
        public DataDeleter(Schema fromSchema, Schema toSchema)
            : base(fromSchema, toSchema)
        {
        }

        #endregion

        #region [ Methods ]

        /// <summary>
        /// Close
        /// </summary>
        public override void Close()
        {
            base.Close();

            if ((object)Disposed != null)
                Disposed(this, EventArgs.Empty);
        }

        /// <summary>
        /// Execute this <see cref="DataDeleter"/>
        /// </summary>
        public override void Execute()
        {
            List<Table> tablesList = new List<Table>();
            Table tableLookup;
            Table table;
            int x;

            if (m_tableCollection.Count == 0)
                Analyze();

            if (m_tableCollection.Count == 0)
                throw new NullReferenceException("No tables to process even after analyze.");

            // We copy the tables into an array list so we can sort and process them in priority order
            foreach (Table sourceTable in m_tableCollection)
            {
                if (sourceTable.Process)
                {
                    sourceTable.CalculateRowCount();

                    if (sourceTable.RowCount > 0)
                    {
                        tablesList.Add(sourceTable);
                        m_overallTotal += sourceTable.RowCount;
                    }
                }
            }

            tablesList.Sort((table1, table2) => table1.Priority > table2.Priority ? 1 : (table1.Priority < table2.Priority ? -1 : 0));

            // Begin deleting data from destination tables - we process deletes in reverse
            // referential integrity order...
            for (x = tablesList.Count - 1; x >= 0; x--)
            {
                table = (Table)tablesList[x];

                // Lookup table name in destination data source
                tableLookup = m_toSchema.Tables.FindByMapName(table.MapName);

                if ((object)tableLookup != null)
                {
                    // We can only do Sql deletes where primary key fields are defined...
                    if (table.RowCount > 0 && (m_useFromSchemaRI ? table.PrimaryKeyFieldCount : tableLookup.PrimaryKeyFieldCount) > 0)
                    {
                        // Inform clients of table deletes
                        OnTableProgress(table.Name, true, x + 1, tablesList.Count);

                        // Delete data from destination table based on source table records
                        ExecuteDeletes(table, tableLookup);
                    }
                    else
                    {
                        // Inform clients of table skip
                        OnTableProgress(table.Name, false, x + 1, tablesList.Count);

                        // If we skipped rows because of lack of key fields, make sure and
                        // synchronize overall progress event
                        if (table.RowCount > 0)
                        {
                            m_overallProgress += table.RowCount;
                            OnOverallProgress((int)m_overallProgress, (int)m_overallTotal);
                        }
                    }
                }
                else
                {
                    // Inform clients of table skip
                    OnTableProgress(table.Name, false, x + 1, tablesList.Count);
                }

                // Perform final update of progress information
                OnTableProgress("", false, tablesList.Count, tablesList.Count);
            }
        }

        /// <summary>
        /// Execute a command to delete data from source to destination table
        /// </summary>
        /// <param name="fromTable">Source table</param>
        /// <param name="toTable">Destination table</param>
        private void ExecuteDeletes(Table fromTable, Table toTable)
        {
            Table sourceTable = m_useFromSchemaRI ? fromTable : toTable;
            Field lookupField;
            Field commonField;

            // Progress process variables
            int progress = 0;
            int totalProgress;

            // Bulk delete variables
            string deleteSQLStub;
            StringBuilder deleteSQL;
            StringBuilder whereSQL;
            string value;
            bool isPrimary;

            // Create a field list of all of the common fields in both tables
            // Use toTable as the parent to retrieve the appropriate SQLEncodedValues
            Fields fieldCollection = new Fields(toTable);

            foreach (Field field in fromTable.Fields)
            {
                // Lookup field name in destination table
                lookupField = toTable.Fields[field.Name];

                if ((object)lookupField != null)
                {
                    // We currently don't handle binary fields...
                    if (!(field.Type == OleDbType.Binary || field.Type == OleDbType.LongVarBinary || field.Type == OleDbType.VarBinary) && !(lookupField.Type == OleDbType.Binary || lookupField.Type == OleDbType.LongVarBinary || lookupField.Type == OleDbType.VarBinary))
                    {
                        // Copy field information from destination field
                        if (m_useFromSchemaRI)
                        {
                            commonField = new Field(field.Name, field.Type);
                            commonField.AutoIncrement = field.AutoIncrement;
                        }
                        else
                        {
                            commonField = new Field(lookupField.Name, lookupField.Type);
                            commonField.AutoIncrement = lookupField.AutoIncrement;
                        }

                        fieldCollection.Add(commonField);
                    }
                }
            }

            // Exit if no common field names were found
            if (fieldCollection.Count == 0)
            {
                m_overallProgress += fromTable.RowCount;
                return;
            }

            totalProgress = fromTable.RowCount;
            OnRowProgress(fromTable.Name, 0, totalProgress);
            OnOverallProgress((int)m_overallProgress, (int)m_overallTotal);

            // Execute source query
            using (IDataReader fromReader = fromTable.Connection.ExecuteReader("SELECT " + fieldCollection.GetList(sqlEscapeFunction: fromTable.Parent.Parent.SQLEscapeName) + " FROM " + fromTable.SQLEscapedName, CommandBehavior.SequentialAccess, Timeout))
            {
                // Create Sql delete stub
                deleteSQLStub = "DELETE FROM " + toTable.SQLEscapedName;

                // Delete data for each row...
                while (fromReader.Read())
                {
                    deleteSQL = new StringBuilder(deleteSQLStub);
                    whereSQL = new StringBuilder();

                    // Coerce needed field data into proper Sql format
                    foreach (Field field in fieldCollection)
                    {
                        try
                        {
                            field.Value = fromReader[field.Name];
                        }
                        catch (Exception ex)
                        {
                            field.Value = "";
                            OnSQLFailure("Failed to get field value for [" + sourceTable.Name + "." + field.Name + "]", ex);
                        }

                        // Check to see if this is a key field
                        lookupField = sourceTable.Fields[field.Name];

                        if ((object)lookupField != null)
                            isPrimary = lookupField.IsPrimaryKey;
                        else
                            isPrimary = false;

                        // We only deal with primary key field values for Sql deletes...
                        if (isPrimary)
                        {
                            value = field.SQLEncodedValue;

                            if (!value.Equals("NULL", StringComparison.CurrentCultureIgnoreCase))
                            {
                                if (whereSQL.Length == 0)
                                    whereSQL.Append(" WHERE ");
                                else
                                    whereSQL.Append(" AND ");

                                whereSQL.Append(field.SQLEscapedName);
                                whereSQL.Append(" = ");
                                whereSQL.Append(value);
                            }
                        }
                    }

                    if (whereSQL.Length > 0)
                    {
                        // Add where criteria to Sql delete statement
                        deleteSQL.Append(whereSQL.ToString());

                        try
                        {
                            // Delete record in destination table
                            toTable.Connection.ExecuteNonQuery(deleteSQL.ToString(), Timeout);
                        }
                        catch (Exception ex)
                        {
                            OnSQLFailure(deleteSQL.ToString(), new DataException("ERROR: No \"WHERE\" criteria was generated for Sql delete statement, primary key value missing?  Delete not performed", ex));
                        }
                    }

                    progress++;
                    m_overallProgress++;

                    if (progress % RowReportInterval == 0)
                    {
                        OnRowProgress(fromTable.Name, progress, totalProgress);
                        OnOverallProgress((int)m_overallProgress, (int)m_overallTotal);
                    }
                }

                fromReader.Close();
            }

            OnRowProgress(fromTable.Name, totalProgress, totalProgress);
            OnOverallProgress((int)m_overallProgress, (int)m_overallTotal);
        }

        #endregion
    }
}