using System.Data;
using System.Collections.Generic;
using System;
using System.Text;
using System.Threading;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using dataladder.AddressVerification;
using dataladder.Data;
using dataladder.Matching;
using dataladder.Matching.Indexing;
using dataladder.Licensing;
using dataladder.Matching.Project;
using dataladder.Data.DataTransformation;
using dataladder.XtraGridHelper;
using DataMatch.Data.Enums;
using System.Diagnostics;

namespace SampleServiceNamespace
{
    public class EngineWrapper : object, IDisposable
    {
        private delegate ITable2CoordsMapper FindMatchesDelegate(string[] values, int records);

        #region Fields

        public String Errors { get; set; }

        private static string importedDataPath = dataladder.Matching.ApplicationSettings.TempDataPath + @"\api_data";
        private static string tempDataPath = dataladder.Matching.ApplicationSettings.TempDataPath + @"\api_temp data";
        private static string resultsDataPath = dataladder.Matching.ApplicationSettings.TempDataPath + @"\api_results";
        private static string finalResultsFileName = System.IO.Path.Combine(resultsDataPath, "final results.txt");
        private static string transformedDataSourceAFileName = System.IO.Path.Combine(resultsDataPath, "transformed data source A.txt");
        private static string pathForRegistrationFile = dataladder.Matching.ApplicationSettings.TempDataPath + @"\REGISTRATION";

        private DbHelper _loadedSqlHelper = null;

        protected readonly String _projectName;
        private String _connectionString;
        private String _tableName;

        protected String _projectsPath;
        private String _appDataPath;
        private String _appTempDataPath;

        private String _accuPath;
        private String _geoPath;

        private String _pkField;

        private static int instanceCounter = 0;
        private int instanceNumber = instanceCounter++;
        protected string dataSourceNameSingleRow = "single row table";
        string dataSourceNameCashed => $"cached table {instanceNumber}";

        protected int dataSourceSearchIndex;
        int dataSourceCacheIndex;
        protected string dataSourceNameLog;

        protected OnDriveTable singleRowTable = null;
		
        protected OnDriveTable importedLogTable = null;
        OnDriveTable cachedTable = null;

        protected DataSourceInfo dataSourceInfoSearchRecord;
        protected DataSourceInfo dataSourceInfoLog;
        DataSourceInfo dataSourceInfoCached;

        protected ProjectInfo projectInfo;
        protected MatchEngine matchEngine;
        MatchEngine matchEngine2;

        //use additional cache table or not
        bool workWithCached = true;
        /// <summary>
        /// use additional cache table
        /// </summary>
        public bool UseCacheTable = false;
        /// <summary>
        /// Allow change thresholds on the fly
        /// </summary>
        public bool FloatThreshold = false;

        //use DataPath, ProjectPath same as in Dme
        bool _applyDmeSettings = true;

        protected List<int> cachedDatasetIndexes = new List<int>();
        List<int> cachedDatasetIndexes2 = new List<int>();

        bool searchInProgress = false;

        DateTime loadTime = DateTime.MinValue;

        //OnDriveTable resultsTable for main table and for cached
        InMemoryTable resultsTable;
        InMemoryTable resultsTable2;

        /// <summary>
        /// List with columns names which are included on MatchDefinition Tab
        /// </summary>
        protected List<string> wantedFields = new List<string>();
            

        
		const int firstCol = 0;

        //const string noAction = "X";
        protected const string singleRowOriginal = "O";

        public string[] MatchingFieldNames;

        public string[] TableFieldNames;
        public object Lock = new object();

        private Dictionary<String, Boolean> _tableFieldQuotesNeeded;
        
        /// <summary>
        /// rows of cached table
        /// </summary>
        private List<string[]> cachedRows = new List<string[]>();
        private int countCachedRows = 0;

        HashSet<string> quotesHashSet = new HashSet<string>();

        private double _level = 0.9;

        protected bool useLog;

        protected bool doTransformation;

        private Boolean _useApi = false;

        public Boolean UseApi
        {
            get { return _useApi; }
            set
            {
                _useApi = value;
                if (matchEngine != null)
                    matchEngine.UseApiAcceleration = _useApi;

            }
        }

        #endregion

        #region Constructors

        public EngineWrapper(string projectName, bool useLog = false, bool doTransformation = false,
            string pathToConfig = "", string conString = "", Boolean useApi = false, Boolean useCache = false)
        {
            Errors = "";
            _useApi = useApi;
            UseCacheTable = useCache;

            //createDirectoryIfNotExist(importedDataPath);
            
			
            string uncompletedPath = Path.Combine(importedDataPath, "uncomplete");
            createDirectoryIfNotExist(uncompletedPath);
            createDirectoryIfNotExist(tempDataPath);
            createDirectoryIfNotExist(resultsDataPath);
            this.useLog = useLog;
            this.doTransformation = doTransformation;

            BuildPaths(projectName, useLog, pathToConfig);

            this._projectName = projectName;
            dataSourceNameSingleRow += projectName;
            dataSourceNameLog = projectName + "Log";
            RegistrationWrapper registrationWrapper = new RegistrationWrapper();
            registrationWrapper.CustomPathForRegistrationFile = pathForRegistrationFile;
            DateTime expirationTime = RegistrationWrapper.ExpirationDate;
            string error;

            loadData(out error);
        }

        public void AddToCacheTableRecord(string[] values)
        {
            AddToCacheTableRecordWithOnlyMatchedFields(values);
        }

        public string[] Pathes;

        private void BuildPaths(string tableName, bool useLog, string pathToConfig)
        {
            dataladder.Matching.ApplicationSettings.Load();

            List<string> forPathes = new List<string>();
            forPathes.Add(pathToConfig);
            IniParser parser = null;
            try
            {
                if (string.IsNullOrEmpty(pathToConfig))
                    parser = new IniParser(Directory.GetCurrentDirectory() + @"\webservice.ini");
                else
                    parser = new IniParser(pathToConfig + @"\webservice.ini");
            }
            catch (FileNotFoundException)
            {
                _projectsPath = dataladder.Matching.ApplicationSettings.ProjectsPath;
                _appDataPath = dataladder.Matching.ApplicationSettings.DataPath;
                _appTempDataPath = dataladder.Matching.ApplicationSettings.TempDataPath;

                _accuPath = dataladder.Matching.ApplicationSettings.AccuPath;
                _geoPath = dataladder.Matching.ApplicationSettings.GeoCodePath;
                //pathForRegistrationFile = dataladder.Matching.ApplicationSettings.
            }

            if (parser != null)
            {
                forPathes.Add("parser not null ");
                _connectionString = parser.GetSetting("AppSettings", "connectionstring");
                if (string.IsNullOrEmpty(_connectionString))
                {
                    //throw new Exception("Connection string not defined");
                }

                if  (!_applyDmeSettings)
                {
                    _projectsPath = parser.GetSetting("AppSettings", "projectsPath");
                    if (string.IsNullOrEmpty(_projectsPath))
                    {
                        throw new Exception("Project path not defined");
                    }

                    _appDataPath = parser.GetSetting("AppSettings", "dataPath");
                    if (string.IsNullOrEmpty(_appDataPath))
                    {
                        throw new Exception("Data path not defined");
                    }

                    _appTempDataPath = parser.GetSetting("AppSettings", "tempDataPath");
                    if (string.IsNullOrEmpty(_appTempDataPath))
                    {
                        throw new Exception("Temp data path not defined");
                    }


                    dataladder.Matching.ApplicationSettings.DataPath = _appDataPath + @"\";
                    dataladder.Matching.ApplicationSettings.TempDataPath = _appTempDataPath + @"\";
                }
                else
                {
                    _projectsPath = dataladder.Matching.ApplicationSettings.ProjectsPath;
                    _appDataPath = dataladder.Matching.ApplicationSettings.DataPath;
                    _appTempDataPath = dataladder.Matching.ApplicationSettings.TempDataPath;
                }

                _accuPath = parser?.GetSetting("AppSettings", "accuPath");
                if (string.IsNullOrEmpty(_accuPath))
                {
                    throw new Exception("Address verification files path not defined");
                }

                _geoPath = parser.GetSetting("AppSettings", "geoPath");
                if (string.IsNullOrEmpty(_geoPath))
                {
                    throw new Exception("Geodata path not defined");
                }

                dataladder.Matching.ApplicationSettings.AccuPath = _accuPath + @"\";
                dataladder.Matching.ApplicationSettings.GeoCodePath = _geoPath + @"\";

                _pkField = parser.GetSetting("PkFieldName", tableName);

                if (useLog)
                {
                    if (string.IsNullOrEmpty(_pkField))
                    {
                        throw new Exception("pKfield not defined");
                    }
                }

                pathForRegistrationFile = parser.GetSetting("AppSettings", "pathForRegistrationFile");
            }

            forPathes.Add(_projectsPath);
            forPathes.Add(_appDataPath);
            forPathes.Add(_appTempDataPath);
            forPathes.Add(_accuPath);
            forPathes.Add(_geoPath);
            Pathes = forPathes.ToArray();
        }

        #endregion

        #region Properties and Fields

        public bool SearchInProgress { get; set; }

        #endregion

        #region Methods

        /// <summary>
        /// for now it assumes all fields are text type
        /// </summary>
        /// <param name="hash"></param>
        /// <param name="tableName"></param>
        /// <param name="values"></param>
        /// <param name="fieldNames"></param>
        /// <param name="error"></param>
        /// <returns></returns>
        public bool InsertRecord(string tableName, string[] values, out string error)
        {
            bool result = false;
            error = "";
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("insert into ");
                sb.AppendLine(tableName);
                sb.AppendLine("(");
                for (int i = 0; i < TableFieldNames.Length - 1; i++) // last field is ID, doesn't used in insert operation
                {
                    sb.Append(TableFieldNames[i]);
                    if (i < TableFieldNames.Length - 2)
                    {
                        sb.AppendLine(",");
                    }
                    else
                    {
                        sb.AppendLine("");
                    }
                }
                sb.AppendLine(")");
                sb.AppendLine("values");
                sb.AppendLine("(");
                for (int i = 0; i < TableFieldNames.Length - 1; i++)
                {
                    //if (tableFieldQuotes[i])
                    {
                        sb.Append("'");
                    }
                    sb.Append(values[i]);
                    //if (tableFieldQuotes[i])
                    {
                        sb.Append("'");
                    }
                    if (i < TableFieldNames.Length - 2)
                    {
                        sb.AppendLine(",");
                    }
                    else
                    {
                        sb.AppendLine("");
                    }
                }
                sb.AppendLine(")");
                executeCmd(sb.ToString());
                //executeCmd($"insert into {tableName} (lastname) values ('{values[0]}')");
            }
            catch (Exception ex)
            {
                error = ex.Message;

                Errors += ex.Message + Environment.NewLine +
                    ex.StackTrace + Environment.NewLine + Environment.NewLine;
            }
            return result;
        }

        /// <summary>
        /// for now it assumes all fields are text type
        /// </summary>
        /// <param name="hash"></param>
        /// <param name="tableName"></param>
        /// <param name="values"></param>
        /// <param name="fieldNames"></param>
        /// <param name="error"></param>
        /// <returns></returns>
        public bool InsertRecord(string[] fieldNames, string[] values, out DataTable inserted, out string error)
        {
            bool result = false;
            error = "";
            inserted = null;
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("insert into ");
                sb.AppendLine(_tableName);
                sb.AppendLine("(");
                for (int i = 0; i < fieldNames.Length; i++)
                {

                    DbHelper dbHelper;
                    if (_loadedSqlHelper == null)
                        dbHelper = new SqlDbHelper(_connectionString);
                    else
                        dbHelper = _loadedSqlHelper;
                    sb.Append(dbHelper.LeftIdentifierQuote);
                    sb.Append(fieldNames[i]);
                    sb.Append(dbHelper.RightIdentifierQuote);

                    if (i < fieldNames.Length - 1)
                    {
                        sb.AppendLine(",");
                    }
                    else
                    {
                        sb.AppendLine("");
                    }
                }
                sb.AppendLine(")");
                sb.AppendLine("values");
                sb.AppendLine("(");
                for (int i = 0; i < values.Length; i++)
                {
                    if (_tableFieldQuotesNeeded[fieldNames[i].ToLower()])
                    {
                        sb.Append("'");
                    }
                    sb.Append(values[i]);
                    if (_tableFieldQuotesNeeded[fieldNames[i].ToLower()])
                    {
                        sb.Append("'");
                    }
                    if (i < values.Length - 1)
                    {
                        sb.AppendLine(",");
                    }
                    else
                    {
                        sb.AppendLine("");
                    }
                }
                sb.AppendLine(")");
                executeCmd(sb.ToString());

                string askAboutInserted = CreateSelectCommand(fieldNames, values);
                inserted = executeQueryCmd(askAboutInserted);
                result = true;
            }
            catch (Exception ex)
            {
                error = ex.Message;

                Errors += ex.Message + Environment.NewLine +
                    ex.StackTrace + Environment.NewLine + Environment.NewLine;
            }
            return result;
        }

        private string CreateSelectCommand(string[] fieldNames, string[] values)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT * FROM ");
            sb.Append(_tableName);
            sb.Append(" WHERE ");

            int cnt = fieldNames.Length < values.Length ? fieldNames.Length : values.Length;

            DbHelper dbHelper;
            if (_loadedSqlHelper == null)
                dbHelper = new SqlDbHelper(_connectionString);
            else
                dbHelper = _loadedSqlHelper;
            for (int i = 0; i < cnt ; i++)
            {
                var columnName = $"{dbHelper.LeftIdentifierQuote}{fieldNames[i]}{dbHelper.RightIdentifierQuote}";
                sb.Append($" {columnName}='{values[i]}' ");

                if (i < cnt - 1)
                {
                    sb.Append("AND");
                }
            }

            sb.Append(";");
            return sb.ToString();
        }

        public bool UpdateRecord(string tableName, long idPrimaryKey, int strongDuplicates, int duplicates, out string error)
        {
            bool result = false;
            error = "";
            try
            {
                string query = $"UPDATE {tableName} SET StrongDuplicate={strongDuplicates}, Duplicate={duplicates} WHERE id={idPrimaryKey};";
                executeCmd(query);
                result = true;
            }
            catch (Exception ex)
            {
                error = ex.Message;

                Errors += ex.Message + Environment.NewLine +
                    ex.StackTrace + Environment.NewLine + Environment.NewLine;
            }
            return result;
        }

        public bool UpdateRecord(string tableName, string[] values, string id, out string error)
        {
            bool result = false;
            error = "";
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("update ");
                sb.AppendLine(tableName);
                sb.Append(" set ");
                for (int i = 0; i < TableFieldNames.Length - 1; i++) // last field is ID, doesn't used in insert operation
                {
                    sb.Append(TableFieldNames[i]);
                    sb.Append(" = ");
                    if (_tableFieldQuotesNeeded[TableFieldNames[i]])
                    {
                        sb.Append("'");
                    }
                    sb.Append(values[i]);
                    if (_tableFieldQuotesNeeded[TableFieldNames[i]])
                    {
                        sb.Append("'");
                    }
                    if (i < TableFieldNames.Length - 2)
                    {
                        sb.AppendLine(",");
                    }
                    else
                    {
                        sb.AppendLine("");
                    }
                }
                sb.Append("where " + _pkField + " = ");
                if (quotesHashSet.Contains(id.ToLower()))
                {
                    sb.Append("'");
                }
                sb.Append(id);
                if (quotesHashSet.Contains(id.ToLower()))
                {
                    sb.Append("'");
                }
                executeCmd(sb.ToString());
            }
            catch (Exception ex)
            {
                error = ex.Message;

                Errors += ex.Message + Environment.NewLine +
                    ex.StackTrace + Environment.NewLine + Environment.NewLine;
            }
            return result;
        }

        public bool DeleteRecord(string tableName, string id, out string error)
        {
            bool result = false;
            error = "";
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("delete from ");
                sb.AppendLine(tableName);
                sb.AppendLine("where " + _pkField + " = ");
                if (quotesHashSet.Contains(id.ToLower()))
                {
                    sb.Append("'");
                }
                sb.Append(id);
                if (quotesHashSet.Contains(id.ToLower()))
                {
                    sb.Append("'");
                }
                executeCmd(sb.ToString());
            }
            catch (Exception ex)
            {
                error = ex.Message;

                Errors += ex.Message + Environment.NewLine +
                    ex.StackTrace + Environment.NewLine + Environment.NewLine;
            }
            return result;
        }

        private void executeCmd(string cmd)
        {
            executeQueryCmd(cmd);
        }

        private DataTable executeQueryCmd(string cmd)
        {
            DataTable res = null;
            DbHelper dbHelper;
            if (_loadedSqlHelper == null)
                dbHelper = new SqlDbHelper(_connectionString);
            else
                dbHelper = _loadedSqlHelper;
            String error;
            dbHelper.Connect(out error);
            res = dbHelper.SelectData(cmd);
            dbHelper.Disconnect();
            return res;
        }

        public OnDriveTable GetImportedTableFromSQL(string connectionString, string cmd, string dataSourceName)
        {
            String error;
            DbHelper reader;
            if (_loadedSqlHelper == null)
                reader = new SqlDbHelper(connectionString);
            else
                reader = _loadedSqlHelper;
            ReaderConfiguration readerConfiguration = reader.GetConfiguration();
            readerConfiguration.SelectCmd = cmd;
            reader.SetConfiguration(readerConfiguration);
            reader.ReadTable(readerConfiguration, true); //--reader.ReadTable(readerConfiguration, true, out error);
            var helper = new ReaderToVariableTableConvertor();
            OnDriveTable result = helper.Copy(reader, importedDataPath, dataSourceName, out error, CancellationToken.None);
            return result;
        }
        
        public static void RemoveRemainedFiles()
        {
            dataladder.IO.IOHelper.RemoveAllFiles(importedDataPath);
            dataladder.IO.IOHelper.RemoveAllFiles(tempDataPath);
            dataladder.IO.IOHelper.RemoveAllFiles(resultsDataPath);
        }
        
        public InMemoryTable FindMatches(string[] values, int records, bool withServiceInfo = true)
        {
            return FindMatches("", values, records, withServiceInfo);
        }

        public InMemoryTable FindMatches(string tableName, string[] values, int records, bool withServiceInfo = true)
        {
            InMemoryTable main = FindMatchesParameter(findMatchesTemp, tableName, values, records, dataSourceSearchIndex, resultsTable);

            if (UseCacheTable)
            {
                InMemoryTable cached = FindMatchesParameter(findMatchesCached, tableName, values, records, dataSourceCacheIndex, resultsTable2);

                main.UnSort();
                AddInMemoryTable(main, cached);

                RowsComparator rowsComparator = new FinalResultRowsComparator(main);
                main.Sort(rowsComparator);
            }

            if (!withServiceInfo)
            {
                //remove service columns, and add "Score" column in the begining
                var mainColumns = main.GetColumnNames();
                var scoreColName = mainColumns.FirstOrDefault(s => s.ToUpper().Equals("SCORE"));
                //columns from original table
                var inputColumns = projectInfo[0].InputTable.GetColumnNames();
                //columns after transformation
                var inputColumns2 = projectInfo[0].TransformedValuesTable.GetColumnNames();
                // and included 
                string[] inputColumns3 = inputColumns2;
                if (wantedFields!= null && wantedFields.Count != 0)
                    inputColumns3 = inputColumns2.Where(col => wantedFields.Contains(col)).ToArray();

                InMemoryTable table = new InMemoryTable("Results");
                string scoreName = "Score";
                string dataSourceName = "Data Source Name";
                string recordName = "Record";
                table.AddField(scoreName, OnDriveTable.StorageDataType.String);
                table.AddField(dataSourceName, OnDriveTable.StorageDataType.String);
                table.AddField(recordName, OnDriveTable.StorageDataType.String);
                table.AddFields(inputColumns3);

                for (int i = 0; i < main.RecordCount; i++)
                {
                    for (int j = 0; j < main.ColumnCount; j++)
                    {
                        string colName = main.GetColumnName(j);
                        if (colName.ToUpper().Equals("SCORE"))
                        {
                            string score = main.GetData(i, j).ToString();
                            if (Double.TryParse(score, out double scr))
                            {
                                Double truncated = (Math.Truncate(scr * 100) / 100);
                                string tmp = string.Format("{0,6:00.00}", truncated);
                                table.SetData(tmp, i, scoreName);
                            }
                            else
                            {
                                table.SetData("-", i, scoreName);
                            }
                        }
                        else if(colName.Equals(dataSourceName) || colName.Equals(recordName))
                        {
                            object obj = main.GetData(i, j);
                            table.SetData(obj.ToString(), i, colName);
                        }
                        else
                        {
                            if (inputColumns2.Contains(colName))
                            {
                                table.SetData(main.GetData(i, j), i, colName);
                            }
                        }
                    }
                }
                return table;
            }

            return main;
        }
        
        public InMemoryTable GetUnitedTable()
        {
            var main = GetMainTable();
            if (UseCacheTable)
            {
                var cached = GetCachedTable();
                AddInMemoryTable(main, cached);
            }
            return main;
        }

        public InMemoryTable GetMainTable()
        {
            return ConvertFromOnDriveToInMemory("Copy of Main Table", projectInfo[0].InputTable);
        }

        public InMemoryTable GetCachedTable()
        {
            return ConvertFromOnDriveToInMemory("Copy of Cached Table", dataSourceInfoCached.InputTable);
        }

        public InMemoryTable ConvertFromOnDriveToInMemory(string name, OnDriveTable table)
        {
            InMemoryTable copy = new InMemoryTable(name, toCheckMemory: false);
            copy.AddFields(table.GetColumnNames());
            AddInMemoryTable(copy, table);
            return copy;
        }

        public Boolean IsInputDataColumn(String columnName)
        {
            return projectInfo[0].InputTable.GetColumnNames().Contains(columnName);
        }

        private void AddInMemoryTable(InMemoryTable result, IContainer2CoordsMapper added)
        {
            //result.UnSort();

            int rowIndex = result.RecordCount;
            for (int i = 0; i < added.RecordCount; i++)
            {
                for (int j = 0; j < added.ColumnCount && j < result.ColumnCount; j++)
                {
                    result.SetData(added.GetData(i, j), rowIndex + i, j);
                }
            }

            //RowsComparator rowsComparator = new FinalResultRowsComparator(result);
            //result.Sort(rowsComparator);
        }

        private InMemoryTable FindMatchesParameter(FindMatchesDelegate findDelegate, string tableName, string[] values, int records, int rejectedSource,
            InMemoryTable results)
        {
            lock (this)
            {
                while (searchInProgress)
                {
                    Thread.Sleep(10);
                }
                searchInProgress = true;
                try
                {
                    ITable2CoordsMapper tmpResults = findDelegate(values, records);
                    HashSet<int> mixedInputRecords;
                    HashSet<int> groupsWithMixedInputRecords = determineGroupsWithMixedInputRecords(values, records, tmpResults, out mixedInputRecords);
                    string[] columnNames = tmpResults.GetColumnNames();
                    InMemoryTable allGroupsResultTable = new InMemoryTable("AllGroupsResult", /*columnNames,*/ toCheckMemory: false);
                    allGroupsResultTable.AddFields(columnNames);
                    copyTmpToUnifiedResults(tmpResults, groupsWithMixedInputRecords, allGroupsResultTable);
                    groupsWithMixedInputRecords.Clear();
                    foreach (int inputRecordIndex in mixedInputRecords)
                    {
                        string[] oneRecordValues = new string[this.MatchingFieldNames.Length];
                        int valuesIndex = inputRecordIndex * this.MatchingFieldNames.Length;
                        for (int i = 0; i < this.MatchingFieldNames.Length; i++)
                        {
                            oneRecordValues[i] = values[valuesIndex++];
                            tmpResults = findDelegate(oneRecordValues, records);
                            copyTmpToUnifiedResults(tmpResults, groupsWithMixedInputRecords, allGroupsResultTable, inputRecordIndex);
                        }
                    }
                    unifyResults(allGroupsResultTable, rejectedSource, ref results);
                }
                finally
                {
                    searchInProgress = false;
                }                
            }

            //resultsTable.Sort<SortableResultRow>();
            RowsComparator rowsComparator = new FinalResultRowsComparator(results);
            results.Sort(rowsComparator);

            return results;
        }

        private static void PrintTable(ITable2CoordsMapper tmpResults)
        {
            for (int i = 0; i < tmpResults.RecordCount; i++)
            {
                for (int j = 0; j < tmpResults.ColumnCount; j++)
                {
                    Console.Write($"{j}.{tmpResults.GetData(i, j)}\t");
                }
                Console.WriteLine();
            }
        }


        private static void FillTable(ITable2CoordsMapper mapper,  InMemoryTable result)
        {
            //int inputRecordIndex = -1;
            int newRowIndex = result.RecordCount;
            //int previousGroupId;
            //if (result.RecordCount > 0)
            //{
            //    previousGroupId = (int)result.GetData(result.RecordCount - 1, (int)PreviewFinalResultsStaticFields.GroupId);
            //}
            //else
            //{
            //    previousGroupId = 0;
            //}
            //int newGroupId = -1;
            for (int row = 0; row < mapper.RecordCount; row++)
            {
                //int groupId = (int)mapper.GetData(row, (int)PreviewFinalResultsStaticFields.GroupId);
                //if (row == 0)
                //{
                //    newGroupId = previousGroupId + 1;
                //}
                //if (groupsWithMixedInputRecords.Contains(groupId))
                //{
                //    continue;
                //}
                //if (groupId != previousGroupId)
                //{
                //    newGroupId = previousGroupId + 1;
                //    previousGroupId = groupId;
                //}
                for (int colIndex = 0; colIndex < mapper.ColumnCount; colIndex++)
                {
                    object obj;
                    string name = mapper.GetColumnName(colIndex);
                    //if ((colIndex == (int)PreviewFinalResultsStaticFields.MatchingRecord) && (inputRecordIndex != -1))
                    //{
                    //    obj = inputRecordIndex;
                    //}
                    //else if (colIndex == (int)PreviewFinalResultsStaticFields.GroupId)
                    //{
                    //    obj = newGroupId;
                    //}
                    //else
                    //{
                    obj = mapper.GetData(row, name);
                    //}
                    result.SetData(obj, newRowIndex, name);
                }
                newRowIndex++;
            }
        }

        private static void copyTmpToUnifiedResults(ITable2CoordsMapper tmpResults,
                                                    HashSet<int> groupsWithMixedInputRecords,
                                                    InMemoryTable allGroupsResultTable,
                                                    int inputRecordIndex = -1)
        {
            int newRowIndex = allGroupsResultTable.RecordCount;
            int previousGroupId;
            if (allGroupsResultTable.RecordCount > 0)
            {
                previousGroupId = (int)allGroupsResultTable.GetData(allGroupsResultTable.RecordCount - 1, (int)PreviewFinalResultsStaticFields.GroupId);
            }
            else
            {
                previousGroupId = 0;
            }
            int newGroupId = -1;
            for (int rowIndex = 0; rowIndex < tmpResults.RecordCount; rowIndex++)
            {
                int groupId = (int)tmpResults.GetData(rowIndex, (int)PreviewFinalResultsStaticFields.GroupId);
                if (rowIndex == 0)
                {
                    newGroupId = previousGroupId + 1;
                }
                if (groupsWithMixedInputRecords.Contains(groupId))
                {
                    continue;
                }
                if (groupId != previousGroupId)
                {
                    newGroupId = previousGroupId + 1;
                    previousGroupId = groupId;
                }
                for (int colIndex = 0; colIndex < tmpResults.ColumnCount; colIndex++)
                {
                    object obj;
                    if ((colIndex == (int)PreviewFinalResultsStaticFields.MatchingRecord) && (inputRecordIndex != -1))
                    {
                        obj = inputRecordIndex;
                    }
                    else if (colIndex == (int)PreviewFinalResultsStaticFields.GroupId)
                    {
                        obj = newGroupId;
                    }
                    else
                    {
                        obj = tmpResults.GetData(rowIndex, colIndex);
                    }
                    allGroupsResultTable.SetData(obj, newRowIndex, colIndex);
                }
                newRowIndex++;
            }
        }

        private HashSet<int> determineGroupsWithMixedInputRecords(string[] values, int records, ITable2CoordsMapper tmpResults, out HashSet<int> mixedInputRecords)
        {
            HashSet<int> result = new HashSet<int>();
            mixedInputRecords = new HashSet<int>();
            Dictionary<int, List<int>> groupsInputRecordsDict = new Dictionary<int, List<int>>();
            // we search for the groups which contain more than one input record
            for (int rowIndex = 0; rowIndex < tmpResults.RecordCount; rowIndex++)
            {
                int datasourceId = (int)tmpResults.GetData(rowIndex, (int)PreviewFinalResultsStaticFields.DataSource);
                int groupId = (int)tmpResults.GetData(rowIndex, (int)PreviewFinalResultsStaticFields.GroupId);
                int matchingRecord;
                if (datasourceId == dataSourceSearchIndex)
                {
                    matchingRecord = (int)tmpResults.GetData(rowIndex, (int)PreviewFinalResultsStaticFields.Record);
                }
                else
                {
                    matchingRecord = (int)tmpResults.GetData(rowIndex, (int)PreviewFinalResultsStaticFields.MatchingRecord);
                }
                List<int> matchingRecordList;
                if (!groupsInputRecordsDict.TryGetValue(groupId, out matchingRecordList))
                {
                    matchingRecordList = new List<int>();
                    groupsInputRecordsDict.Add(groupId, matchingRecordList);
                }
                if (!matchingRecordList.Contains(matchingRecord))
                {
                    matchingRecordList.Add(matchingRecord);
                }
            }
            foreach (KeyValuePair<int, List<int>> keyValuePair in groupsInputRecordsDict)
            {
                List<int> matchingRecordList = keyValuePair.Value;
                if (matchingRecordList.Count > 1)
                {
                    int groupId = keyValuePair.Key;
                    result.Add(groupId);
                    for (int i = 0; i < matchingRecordList.Count; i++)
                    {
                        int matchingRecord = matchingRecordList[i];
                        mixedInputRecords.Add(matchingRecord);
                    }
                }
            }
            return result;
        }

        private ITable2CoordsMapper findMatchesTemp(string[] values, int records)
        {
            dataSourceInfoSearchRecord.InputTable.MakeWritable();
            dataSourceInfoSearchRecord.InputTable.Clear(keepFields: true);
            for (int i = 0; i < values.Length; i++)
            {
                int rowIndex = i / MatchingFieldNames.Length;
                dataSourceInfoSearchRecord.InputTable.SetData(values[i], rowIndex, MatchingFieldNames[i % MatchingFieldNames.Length]);
            }
            if (doTransformation)
            {
                dataSourceInfoSearchRecord.DiagramGenerator.DiagramVariableTableHelper.Processed = false; ;
                dataSourceInfoSearchRecord.RunTransformation();
            }
            if (useLog)
            {
                importedLogTable = importLogTable();
                dataSourceInfoLog.InputTable.ReplaceWith(importedLogTable);
                dataSourceInfoLog.InputTable.MakeReadOnlyShareable();
            }
            if (FloatThreshold)
            {
                if (matchEngine.MultipleMatchDefinitionsManager != null &&
                    matchEngine.MultipleMatchDefinitionsManager.Count > 0)
                {
                    MatchCriteriaList matchDefinitionList = matchEngine.MultipleMatchDefinitionsManager[0];
                    for (Int32 i = 0; i < matchDefinitionList.Count; i++)
                    {
                        matchDefinitionList[i].Fuzzy = true;
                        matchDefinitionList[i].Level = _level;
                    }
                }
            }
            matchEngine.ReindexSingleDataSource(dataSourceSearchIndex);
            matchEngine.LoadAllInMemory = true;
            matchEngine.DoMatch(records, clearAllAfterMatching: false);
            matchEngine.ProcessFinalResults(clearAllAfterMatching: false);
            return matchEngine.FinalScoresGroupsTable;
        }

        public void SetThreshold(double level)
        {
            FloatThreshold = true;
            _level = level;
        }


        private void SetThreshold(MatchEngine matchEngine, double? level)
        {
            if (matchEngine == null) return;

            if (level.HasValue &&
                matchEngine.MultipleMatchDefinitionsManager != null &&
                matchEngine.MultipleMatchDefinitionsManager.Count > 0)
            {
                MatchCriteriaList matchDefinitionList = matchEngine.MultipleMatchDefinitionsManager[0];
                for (Int32 i = 0; i < matchDefinitionList.Count; i++)
                {
                  matchDefinitionList[i].Fuzzy = true;
                  matchDefinitionList[i].Level = level.Value;
                }
                matchEngine.DoIndex();
            }
        }

        public void AddToCacheTableRecordWithOnlyMatchedFields(string[] values)
        {
            if (!UseCacheTable) return;
            
            dataSourceInfoCached.InputTable.MakeWritable();

            int ii = countCachedRows++;
            for (int i = 0; i < values.Length; i++)
            {
                dataSourceInfoCached.InputTable.SetData(values[i], ii, MatchingFieldNames[i % MatchingFieldNames.Length]);
            }

            if (doTransformation)
            {
                dataSourceInfoCached.DiagramGenerator.DiagramVariableTableHelper.Processed = false; ;
                dataSourceInfoCached.RunTransformation();
            }
            matchEngine2.ReindexSingleDataSource(dataSourceCacheIndex);
        }

        public void AddToCacheTableRecord(DataTable table)
        {
            if (!UseCacheTable) return;

            dataSourceInfoCached.InputTable.MakeWritable();

            string[] columnsDestination = dataSourceInfoCached.InputTable.GetColumnNames();
            List<string> columnsSource = new List<string>();
            for (int i = 0; i < table.Columns.Count; i++)
            {
                columnsSource.Add(table.Columns[i].ColumnName);
            }

            int newRowIndex = countCachedRows++;
            DataRow row = table.Rows[0];
            bool somethingWasInserted = false;

            for (int i = 0; i < table.Columns.Count; i++)
            {
                if (columnsDestination.Contains(columnsSource[i]))
                {
                    var eee = row[columnsSource[i]];
                    dataSourceInfoCached.InputTable.SetData(row[columnsSource[i]].ToString(), newRowIndex, columnsSource[i]);
                    somethingWasInserted = true;
                }
            }

            if(!somethingWasInserted)
                dataSourceInfoCached.InputTable.SetData(null, newRowIndex, 0);

            if (doTransformation)
            {
                dataSourceInfoCached.DiagramGenerator.DiagramVariableTableHelper.Processed = false; ;
                dataSourceInfoCached.RunTransformation();
            }
            matchEngine2.ReindexSingleDataSource(dataSourceCacheIndex);
        }

        private ITable2CoordsMapper findMatchesCached(string[] values, int records)
        {
            dataSourceInfoSearchRecord.InputTable.MakeWritable();
            dataSourceInfoSearchRecord.InputTable.Clear(keepFields: true);
            for (int i = 0; i < values.Length; i++)
            {
                int rowIndex = i / MatchingFieldNames.Length;
                dataSourceInfoSearchRecord.InputTable.SetData(values[i], rowIndex, MatchingFieldNames[i % MatchingFieldNames.Length]);
            }
            if (doTransformation)
            {
                dataSourceInfoSearchRecord.DiagramGenerator.DiagramVariableTableHelper.Processed = false; ;
                dataSourceInfoSearchRecord.RunTransformation();
            }

            if (FloatThreshold)
            {
                if (matchEngine2.MultipleMatchDefinitionsManager != null &&
                    matchEngine2.MultipleMatchDefinitionsManager.Count > 0)
                {
                    MatchCriteriaList matchDefinitionList = matchEngine2.MultipleMatchDefinitionsManager[0];
                    for (Int32 i = 0; i < matchDefinitionList.Count; i++)
                    {
                        matchDefinitionList[i].Fuzzy = true;
                        matchDefinitionList[i].Level = _level;
                    }
                }
            }

            matchEngine2.ReindexSingleDataSource(dataSourceSearchIndex);
            matchEngine2.LoadAllInMemory = true;
            matchEngine2.DoMatch(records, clearAllAfterMatching: false);
            matchEngine2.ProcessFinalResults(clearAllAfterMatching: false);
            return matchEngine2.FinalScoresGroupsTable;
        }

        protected virtual MatchEngine initializeMatchingEngine()
        {
            projectInfo = new ProjectInfo();
            string proj = Path.Combine(_projectsPath, /*@"\" + */_projectName + ".dmeproj");
            projectInfo.Load(proj);
            if (projectInfo.DataSourceCount > 1)
            {
                throw new Exception("This example supports only one data source in the project.");
            }
            DataSourceInfo originalDataSourceInfo = projectInfo[0];
            _tableName = originalDataSourceInfo.InputTable.Name;
            if (originalDataSourceInfo.Reader is DbHelper)
            {
                _loadedSqlHelper = originalDataSourceInfo.Reader as DbHelper;
                _tableName = (originalDataSourceInfo.Reader as DbHelper)?.OriginalTableName;
            }

            String error;
            originalDataSourceInfo.Refresh(projectInfo.DataPath, Path.Combine(projectInfo.DataPath, "uncomplete"), out error);
            originalDataSourceInfo.CreateDiagram();
            originalDataSourceInfo.RunTransformation();

            dataSourceInfoSearchRecord = new DataSourceInfo(null, new AddressVerificationSettings());
            dataSourceInfoLog = new DataSourceInfo(null, new AddressVerificationSettings());

            if (singleRowTable != null)
            {
                singleRowTable.Dispose();
            }
            singleRowTable = createSingleRowTableAndDetermineFieldNames(projectInfo);
            for (int colIndex = 0; colIndex < MatchingFieldNames.Length; colIndex++)
            {
                string fieldName = MatchingFieldNames[colIndex];
                singleRowTable.SetData("zzzzzzzzz", 0, fieldName);
            }
            if (useLog)
            {
                singleRowTable.SetData(singleRowOriginal, 0, "ACTION");
            }

            dataSourceInfoSearchRecord.InputTable = singleRowTable;
            projectInfo.Add(dataSourceInfoSearchRecord);

            if (useLog)
            {
                dataSourceInfoLog.InputTable = importedLogTable;
                projectInfo.Add(dataSourceInfoLog);
            }

            if (doTransformation)
            {
                // copying the data transformation rules to the log records
                dataSourceInfoSearchRecord.ColumnTransformationList.Copy(originalDataSourceInfo.ColumnTransformationList);
                dataSourceInfoSearchRecord.CreateDiagram();
                dataSourceInfoSearchRecord.RunTransformation();
                if (useLog)
                {
                    dataSourceInfoLog.ColumnTransformationList.Copy(originalDataSourceInfo.ColumnTransformationList);
                }
            }

            MatchDefinitionBuilder matchDefinitionBuilder = projectInfo.MatchDefinitionBuilder;

            // engine is prepared to support multiple match definitions, here we work only with one
            MultipleMatchDefinitionsManager multipleMatchDefinitionsManager = matchDefinitionBuilder.MultipleMatchDefinitionsManager;

            string firstTableInTheproject = projectInfo[0].InputTable.Name;

            for (int i = 0; i < multipleMatchDefinitionsManager.Count; i++)
            {
                MatchCriteriaList matchDefinitionsList = multipleMatchDefinitionsManager[i];
                for (int singleDefinitionIndex = 0; singleDefinitionIndex < matchDefinitionsList.Count; singleDefinitionIndex++)
                {
                    MatchCriteria matchDefinitionSingle = matchDefinitionsList[singleDefinitionIndex];
                    bool found;
                    string fieldName = matchDefinitionSingle.GetMappedFieldName(firstTableInTheproject, out found);
                    if (found) // in case of multiple definitions it could be found == false
                    {
                        matchDefinitionSingle.MapField(dataSourceNameSingleRow, fieldName);
                        if (useLog)
                        {
                            matchDefinitionSingle.MapField(dataSourceNameLog, fieldName);
                        }
                    }
                }
            }

			
			
			
			
			
			
			
			
			
			
			

            matchDefinitionBuilder.InitialMapping();

            MatchEngine matchEngine = projectInfo.CreateMatchEngine();
            matchEngine.AllRecordsInGroupMustBeSimilar = false;

            matchEngine.DataSourceIndexPairList?.Clear(); // they are loaded with the project...
            matchEngine.DoIndex();
            dataSourceSearchIndex = projectInfo.DataSourceCount - 1;
            for (int i = 0; i < projectInfo.DataSourceCount - 1; i++)
            {
                matchEngine.AddPairToMatchList(i, dataSourceSearchIndex); // to find matches between data sources A and B
            }
            if (useLog)
            {
                matchEngine.AddPairToMatchList(1, 2); // to find matches between data sources B and C
            }
            cachedDatasetIndexes.Clear();
            cachedDatasetIndexes.Add(0);

            matchEngine.SetCachedDataSources(cachedDatasetIndexes);

            matchEngine.LoadAllInMemory = true;
            matchEngine.DoMatch(clearAllAfterMatching: false);
            matchEngine.ProcessFinalResults(clearAllAfterMatching: false);

            List<string> tmp = new List<string>();
            TableFieldNames = new string[matchDefinitionBuilder.AvailableFields.MappedFieldsRowList.Count];

            for (int i = 0; i < matchDefinitionBuilder.AvailableFields.MappedFieldsRowList.Count; i++)
            {
                MappedFieldsRow mappedFieldsRow = matchDefinitionBuilder.AvailableFields.MappedFieldsRowList[i];
                FieldMapInfo fieldMapInfo = mappedFieldsRow[this._tableName];
                if (fieldMapInfo != null)
                {
                    string fieldName = fieldMapInfo.FieldName.Trim();

                    if (!string.IsNullOrEmpty(fieldName))
                    {
                        tmp.Add(fieldName);
                    }
                }
            }

            TableFieldNames = tmp.ToArray();
            DetermineSqlFieldTypes();
			
            return matchEngine;
        }
        
        private MatchEngine initializeMatchingEngine2()
        {
            ProjectInfo projectInfo = new ProjectInfo();
            projectInfo.Load(_projectsPath + @"\" + _projectName + ".dmeproj", loadResults:false);
            if (projectInfo.DataSourceCount > 1)
            {
                throw new Exception("this example supports only one data source in the project.");
            }
            DataSourceInfo originalDataSourceInfo = projectInfo[0];
            string cachedName = "Cached table for " + _projectName;
            projectInfo.ProjectName = cachedName;
            projectInfo.Save(_projectsPath + @"\" + cachedName, true);
            PrepareCachedDataSource(originalDataSourceInfo);

            projectInfo.Add(dataSourceInfoCached);
            projectInfo.Add(dataSourceInfoSearchRecord);

            MatchDefinitionBuilder matchDefinitionBuilder = projectInfo.MatchDefinitionBuilder;

            // engine is prepared to support multiple match definitions, here we work only with one
            MultipleMatchDefinitionsManager multipleMatchDefinitionsManager = projectInfo.MatchDefinitionBuilder.MultipleMatchDefinitionsManager;

            string firstTableInTheproject = projectInfo[0].InputTable.Name;

            for (int i = 0; i < multipleMatchDefinitionsManager.Count; i++)
            {
                MatchCriteriaList matchDefinitionsList = multipleMatchDefinitionsManager[i];
                for (int singleDefinitionIndex = 0; singleDefinitionIndex < matchDefinitionsList.Count; singleDefinitionIndex++)
                {
                    MatchCriteria matchDefinitionSingle = matchDefinitionsList[singleDefinitionIndex];
                    bool found;
                    string fieldName = matchDefinitionSingle.GetMappedFieldName(firstTableInTheproject, out found);
                    if (found) // in case of multiple definitions it could be found == false
                    {
                        matchDefinitionSingle.MapField(dataSourceNameSingleRow, fieldName);
                        matchDefinitionSingle.MapField(dataSourceNameCashed, fieldName);
                        if (useLog)
                        {
                            matchDefinitionSingle.MapField(dataSourceNameLog, fieldName);
                        }
                    }
                }
            }

            //projectInfo.Remove(0);
            matchDefinitionBuilder.InitialMapping();
            MatchEngine matchEngine = projectInfo.CreateMatchEngine();
            matchEngine.AllRecordsInGroupMustBeSimilar = false;

            matchEngine.DataSourceIndexPairList?.Clear(); // they are loaded with the project...
            matchEngine.DoIndex();
            dataSourceCacheIndex = 2;
            matchEngine.AddPairToMatchList(1, 2);

            cachedDatasetIndexes2.Clear();
            cachedDatasetIndexes2.Add(0);

            matchEngine.SetCachedDataSources(cachedDatasetIndexes2);

            matchEngine.LoadAllInMemory = true;
            matchEngine.DoMatch(clearAllAfterMatching: false);
            matchEngine.ProcessFinalResults(clearAllAfterMatching: false);

            return matchEngine;
        }

        private void PrepareCachedDataSource(DataSourceInfo originalDataSourceInfo)
        {
            dataSourceInfoCached = new DataSourceInfo(null, new AddressVerificationSettings());

            if (cachedTable != null)
            {
                cachedTable.Dispose();
            }

            cachedTable = new OnDriveTable(importedDataPath, dataSourceNameCashed, toDeleteExisting: true);
            string[] allFieldNames = projectInfo[0].InputTable.GetColumnNames();
            int fieldsCount = allFieldNames.Length;
            for (int i = 0; i < allFieldNames.Length; i++)
            {
                cachedTable.AddField(allFieldNames[i], typeof(string));
            }

            for (int colIndex = 0; colIndex < allFieldNames.Length; colIndex++)
            {
                string fieldName = allFieldNames[colIndex];
                cachedTable.SetData("", 0, fieldName);
            }

            dataSourceInfoCached.InputTable = cachedTable;

            if (doTransformation)
            {
                dataSourceInfoCached.ColumnTransformationList.Copy(originalDataSourceInfo.ColumnTransformationList);
                dataSourceInfoCached.CreateDiagram();
                dataSourceInfoCached.RunTransformation();
            }
        }

        private void DetermineSqlFieldTypes()
        {
            _tableFieldQuotesNeeded = new Dictionary<String, Boolean>();
            quotesHashSet = new HashSet<string>();
            string cmd = "select t.name as typename, c.name as columnname from syscolumns c\r\n" +
                         "left join sysobjects o on (c.id = o.id) left join systypes t on (c.xtype = t.xtype)\r\n" +
                         "where t.status = 0 and o.name = '" + this._tableName + "'";
            OnDriveTable typesTable = GetImportedTableFromSQL(_connectionString, cmd, "types");
            for (int rowIndex = 0; rowIndex < typesTable.RecordCount; rowIndex++)
            {
                string typeName = typesTable.GetData(rowIndex, 0).ToString().ToLower();
                if (typeName.Contains("char") || typeName.Contains("date"))
                {
                    string columnName = typesTable.GetData(rowIndex, 1).ToString().ToLower();
                    quotesHashSet.Add(columnName);
                }
            }
            for (int i = 0; i < TableFieldNames.Length; i++)
            {
                string fieldName = TableFieldNames[i].ToLower();
                _tableFieldQuotesNeeded[fieldName] = quotesHashSet.Contains(fieldName);
            }
        }

        protected OnDriveTable createSingleRowTableAndDetermineFieldNames(ProjectInfo projectInfo)
        {
            OnDriveTable result = new OnDriveTable(importedDataPath, dataSourceNameSingleRow, toDeleteExisting: true);
            List<string> allFieldNames = determineFieldNames(projectInfo);
            int fieldsCount = allFieldNames.Count;
            MatchingFieldNames = new string[fieldsCount];
            for (int i = 0; i < allFieldNames.Count; i++)
            {
                string fieldName = allFieldNames[i];
                MatchingFieldNames[i] = fieldName;
                result.AddField(fieldName, typeof(string));
            }
            if (useLog)
            {
                result.AddField("ACTION", typeof(string));
            }
            return result;
        }

        private List<string> determineFieldNames(ProjectInfo projectInfo)
        {
            DataSourceInfo originalDataSourceInfo = projectInfo[0];
            string tableName = originalDataSourceInfo.Name;
            TransformationDiagram transformationDiagram = originalDataSourceInfo.DiagramGenerator.DiagramVariableTableHelper.FirstTransformationDiagram;
            List<string> tmp = new List<string>();
            MultipleMatchDefinitionsManager multipleMatchDefinitionsManager = projectInfo.MatchDefinitionBuilder.MultipleMatchDefinitionsManager;
            for (int matchDefinitionIndex = 0; matchDefinitionIndex < multipleMatchDefinitionsManager.Count; matchDefinitionIndex++)
            {
                List<MatchDefinitionMappedToField> matchDefinitionMappedToFieldList = multipleMatchDefinitionsManager[matchDefinitionIndex].DetermineFieldsToIndex(originalDataSourceInfo.Name, originalDataSourceInfo.TransformedValuesTable);
                for (int i = 0; i < matchDefinitionMappedToFieldList.Count; i++)
                {
                    MatchDefinitionMappedToField matchDefinitionMappedToField = matchDefinitionMappedToFieldList[i];
                    string fieldName = matchDefinitionMappedToField.FieldName;
                    DataFlow output = transformationDiagram.Outputs[fieldName];
                    if (output != null)
                    {
                        if (doTransformation)
                        {

                            for (int ii = 0; ii < transformationDiagram.Inputs.Count; ii++)
                            {
                                DataFlow input = transformationDiagram.Inputs[ii];
                                if ((output.IsAfter(input)) | (input == output))
                                {
                                    if (!tmp.Contains(input.Name))
                                    {
                                        tmp.Add(input.Name);
                                    }
                                }
                            }
                        }
                        else
                        {
                            if (!tmp.Contains(output.Name))
                            {
                                tmp.Add(output.Name);
                            }
                        }
                    }
                }
            }
            return tmp;
        }

        private void unifyResults(InMemoryTable combinedResults, int rejectedSourceIndex, ref InMemoryTable resultsTable)
        {
            if (resultsTable != null)
            {
                resultsTable.Dispose();
            }
            //resultsTable = new OnDriveTable(importedDataPath, this.matchEngine.Name + " results", toDeleteExisting: true);
            resultsTable = new InMemoryTable("Results Table", /*new string[] { }, */toCheckMemory: false);
            int count = useLog ? combinedResults.ColumnCount - 2 : combinedResults.ColumnCount;
            for (int colIndex = firstCol; colIndex < count; colIndex++) // without action and last update fields
            {
                string fieldName = combinedResults.GetColumnName(colIndex);
                resultsTable.AddField(fieldName, OnDriveTable.StorageDataType.Other);
            }
            string id;
            string action;
            Dictionary<string, string> logRecords = new Dictionary<string, string>();
            if (useLog)
            {
                for (int rowIndex = 0; rowIndex < dataSourceInfoLog.InputTable.RecordCount; rowIndex++) // record 0 is the lookup record itself...
                {
                    id = dataSourceInfoLog.InputTable.GetData(rowIndex, _pkField).ToString();
                    action = (string)dataSourceInfoLog.InputTable.GetData(rowIndex, "ACTION");
                    logRecords.Add(id, action);
                }
            }
            for (int rowIndex = 0; rowIndex < combinedResults.RecordCount; rowIndex++)
            {
                int dataSourceIndex = (int)combinedResults.GetData(rowIndex, (int)PreviewFinalResultsStaticFields.DataSource);
                //if (action == singleRowOriginal) // this is the lookup record itself...
                if (dataSourceIndex == rejectedSourceIndex)
                {
                    continue;
                }
                if (useLog)
                {
                    id = combinedResults.GetData(rowIndex, _pkField).ToString().Trim();
                    action = combinedResults.GetData(rowIndex, "ACTION").ToString();
                    if (!string.IsNullOrEmpty(action)) // for log records
                    {
                        if (action == "D") // record is from log, record deleted, ignore it...
                        {
                            continue;
                        }
                        else
                        {
                            copyResultRecord(combinedResults, rowIndex, resultsTable);
                        }
                    }
                    else // not a log record
                    {
                        id = combinedResults.GetData(rowIndex, _pkField).ToString();
                        if (logRecords.TryGetValue(id, out action)) // original record, changed ignore it...
                        {
                            continue;
                        }
                        else
                        {
                            copyResultRecord(combinedResults, rowIndex, resultsTable);
                        }
                    }
                }
                else
                {
                    copyResultRecord(combinedResults, rowIndex, resultsTable);
                }
            }
        }

        private void copyResultRecord(InMemoryTable combinedResults, int rowIndex, InMemoryTable resultsTable)
        {
            int newRowIndex = resultsTable.RecordCount;
            int count = useLog ? combinedResults.ColumnCount - 2 : combinedResults.ColumnCount;
            for (int colIndex = firstCol; colIndex < count; colIndex++) // without action and last update fields
            {
                object obj = combinedResults.GetData(rowIndex, colIndex);
                resultsTable.SetData(obj, newRowIndex, colIndex - firstCol);
            }
        }

        private void loadData(out string error)
        {
            error = "";
            System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();
            stopwatch.Start();

            projectInfo = new ProjectInfo();
            if (matchEngine != null)
            {
                matchEngine.Dispose();
                matchEngine = null;
            }
            if (matchEngine2 != null)
            {
                matchEngine2.Dispose();
                matchEngine2 = null;
            }
            loadTime = DateTime.Now;

            if (useLog)
            {
                importedLogTable = importLogTable();
            }
            try
            {
                matchEngine = initializeMatchingEngine();
                matchEngine.UseApiAcceleration = _useApi;
                if (workWithCached && UseCacheTable)
                {
                    matchEngine2 = initializeMatchingEngine2();
                }
            }
            catch (Exception ex)
            {
                error = ex.Message;
                Errors += ex.Message + Environment.NewLine + 
                    ex.StackTrace + Environment.NewLine;
            }
        }

        private OnDriveTable importLogTable()
        {
            string cmd = "select l.* from " + _tableName + "log l " +
                         "right join (select " + _pkField + ", max(lastupdate) as lastupdate from " + _tableName + "log group by " + _pkField + ") l2 on l." + _pkField + " = l2." + _pkField + " and l.lastupdate = l2.lastupdate " +
                         "where l.lastupdate > " +
                         "'" + SqlDbHelper.DateTimeToString(loadTime) + "'";
            // OracleDbHelper.DateTimeToString(loadTime);
            OnDriveTable result = GetImportedTableFromSQL(_connectionString, cmd, dataSourceNameLog);
            return result;
        }

        private static bool createDirectoryIfNotExist(string dir)
        {
            bool result = Directory.Exists(dir);
            if (!result)
            {
                try
                {
                    Directory.CreateDirectory(dir);
                    result = true;
                }
                catch
                {
                    result = false;
                }
            }
            return result;
        }

        #endregion

        #region IDisposable members

        public void Dispose()
        {
            //if (singleRowTable != null)
            //{
            //    singleRowTable.Dispose();
            //    singleRowTable = null;
            //}
            //if (importedLogTable != null)
            //{
            //    importedLogTable.Dispose();
            //    importedLogTable = null;
            //}
            //if (importedTableB != null)
            //{
            //  importedTableB.Dispose();
            //  importedTableB = null;
            //}
            //this.matchEngine.Dispose();
            //matchEngine?.Dispose();

            cachedTable?.Dispose();
            singleRowTable?.Dispose();
            importedLogTable?.Dispose();
            resultsTable?.Dispose();
            resultsTable2?.Dispose();


            matchEngine?.Dispose();
            matchEngine2?.Dispose();
            _loadedSqlHelper = null;
            projectInfo?.Dispose();
            GC.Collect();
        }

        #endregion
    }

    public class FinalResultRowsComparator : RowsComparator
    {
        public FinalResultRowsComparator(IContainer2CoordsMapper mapper)
            : base(mapper)
        {
        }

        public override Int32 Compare(Object x, Object y)
        {
            Int32 result = 0;

            Int32 rowIndex1 = (Int32) x;
            Int32 rowIndex2 = (Int32) y;

            Double score1 = (Double) Mapper.GetData(rowIndex1, (Int32) PreviewFinalResultsStaticFields.Score);  // Maybe it's Score field. Now it's 12
            Double score2 = (Double) Mapper.GetData(rowIndex2, (Int32) PreviewFinalResultsStaticFields.Score);

            if (score1 < score2)
            {
                result = 1;
            }
            else if (score1 > score2)
            {
                result = -1;
            }
            else
            {
                Int32 recordIndex1 = (Int32) Mapper.GetData(rowIndex1, (Int32) PreviewFinalResultsStaticFields.Record);
                Int32 recordIndex2 = (Int32) Mapper.GetData(rowIndex2, (Int32) PreviewFinalResultsStaticFields.Record);

                if (recordIndex1 < recordIndex2)
                {
                    result = -1;
                }
                else if (recordIndex1 > recordIndex2)
                {
                    result = 1;
                }
                else
                {
                    Int32 matchingRecordIndex1 = (Int32) Mapper.GetData(rowIndex1, (Int32) PreviewFinalResultsStaticFields.MatchingRecord);
                    Int32 matchingRecordIndex2 = (Int32) Mapper.GetData(rowIndex2, (Int32) PreviewFinalResultsStaticFields.MatchingRecord);

                    if (matchingRecordIndex1 < matchingRecordIndex2)
                    {
                        result = -1;
                    }
                    else if (matchingRecordIndex1 > matchingRecordIndex2)
                    {
                        result = 1;
                    }
                }
            }

            return result;
        }
    }
}