using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;
using DTS.Common.Storage;
using DTS.Common.Utilities.Logging;

namespace DTS.Common.ISO
{
    /// <summary>
    /// this is a helper class wrapping access to the iso13499 access db
    /// it also now makes use of datapro database tables which mimic the access db
    /// </summary>
    public class ISO13499FileDb
    {
        public class ExpiredISOFieldException : Exception
        {
            public ExpiredISOFieldException(string remark) : base(remark) { }
        }
        #region dictionaries
        /// <summary>
        /// list of directions, populated when first loaded
        /// </summary>
        private readonly Dictionary<string, MMEDirections> _directionsDictionary = new Dictionary<string, MMEDirections>();
        /// <summary>
        /// list of filter classes, populated when first loaded
        /// </summary>
        private readonly Dictionary<string, MMEFilterClasses> _filterClassesDictionary = new Dictionary<string, MMEFilterClasses>();
        /// <summary>
        /// list of all known fine 1 locations, populated when first loaded 
        /// </summary>
        private readonly Dictionary<string, MMEFineLocations1> _fineLoc1Dictionary = new Dictionary<string, MMEFineLocations1>();
        /// <summary>
        /// list of all known fine 2 locations, populated when first loaded
        /// </summary>
        private readonly Dictionary<string, MMEFineLocations2> _fineLoc2Dictionary = new Dictionary<string, MMEFineLocations2>();
        /// <summary>
        /// list of all known fine 3 locations, populated when first loaded
        /// </summary>
        private readonly Dictionary<string, MMEFineLocations3> _fineLoc3Dictionary = new Dictionary<string, MMEFineLocations3>();
        /// <summary>
        /// list of all known physical dimensions, populated when first loaded
        /// </summary>
        private readonly Dictionary<string, MMEPhysicalDimensions> _physicalDimensionsDictionary = new Dictionary<string, MMEPhysicalDimensions>();
        /// <summary>
        /// list of all known positions, populated when first loaded
        /// </summary>
        private readonly Dictionary<string, MMEPositions> _positionsDictionary = new Dictionary<string, MMEPositions>();
        /// <summary>
        /// list of all known possible channels, populated when first loaded
        /// </summary>
        private readonly List<MMEPossibleChannels> _possibleChannels = new List<MMEPossibleChannels>();

        private readonly Dictionary<string, List<MMEPossibleChannels>> _possibleChannelsByType =
            new Dictionary<string, List<MMEPossibleChannels>>();
        /// <summary>
        /// list of possible test objects, populated when first loaded
        /// </summary>
        private readonly Dictionary<string, MMETestObjects> _testObjectsDictionary = new Dictionary<string, MMETestObjects>();
        /// <summary>
        /// list of all possible transducer locations, populated when first loaded
        /// </summary>
        private readonly Dictionary<string, MMETransducerMainLocation> _transducerMainLoc = new Dictionary<string, MMETransducerMainLocation>();
        #endregion
        /*
        public string GetLocalConnectionString()
        {
            lock (dbLock)
            {
                if (null != _localConnection) return _localConnection;
                if (null == Server && !_bCS3) { throw new Exception("db connection not initialized"); }
                if (_bCS3)
                {
                    _localConnection = CS3UsingNTLMAuthentication ? $"Server={CS3Host};Database={CS3Name};Trusted_Connection=TRUE;" 
                        : $"Server={CS3Host};Database={CS3Name};User Id={CS3User};Password={CS3Password}";
                }
                else
                {
                    _localConnection = _usingNTLMAuthentication
                        ? $"Server={Server};Database={DBName};Trusted_Connection=TRUE;"
                        : $"Server={Server};Database={DBName};User Id={Username};Password={Password};";
                }
            }
            return _localConnection;
        }*/

        /// <summary>
        /// constructs an iso13499file db
        /// </summary>
        public ISO13499FileDb()
        {
        }

        /// <summary>
        /// scans all possible channels for unique types, returns the list of unique types
        /// </summary>
        private List<string> _uniquePossibleChannelTypes = null;
        public string[] GetUniquePossibleChannelTypes()
        {
            RefreshIfNeeded();
            lock (RefreshLock)
            {
                if (null != _uniquePossibleChannelTypes) return _uniquePossibleChannelTypes.ToArray();
                _uniquePossibleChannelTypes = new List<string>();
                var uniquetypes = (from pc in _possibleChannels where !pc.Expired orderby pc.Text_L1 select pc.Type)
                    .Distinct().ToArray();
                if (uniquetypes.Length > 0)
                {
                    _uniquePossibleChannelTypes.AddRange(uniquetypes);
                    _uniquePossibleChannelTypes.Sort();
                }
                return _uniquePossibleChannelTypes.ToArray();
            }
        }
        public string[] GetUniquePossibleChannelTypes(string typeToRemove)
        {
            lock (RefreshLock)
            {
                return GetUniquePossibleChannelTypes()
                    .Where(uniquePossibleChannelType => !uniquePossibleChannelType.StartsWith(typeToRemove)).ToArray();
            }
        }
        private void RefreshIfNeeded()
        {
            lock (RefreshLock)
            {
                if (!_bLoaded) { RefreshAllData(); }
            }
        }

        /// <summary>
        /// gets a list of all possible channels given a type
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public MMEPossibleChannels[] GetPossibleChannelsForType(string type)
        {
            RefreshIfNeeded();
            lock (RefreshLock)
            {
                if (!_possibleChannelsByType.ContainsKey(type))
                {
                    return new MMEPossibleChannels[0];
                }
                return _possibleChannelsByType[type].ToArray();
            }
        }

        /// <summary>
        /// gets the possible channels just from DataPRO exclusive (so excluding ISO13499 origined channels)
        /// </summary>
        /// <returns></returns>
        public MMEPossibleChannels[] GetSQLPossibleChannels()
        {
            RefreshIfNeeded();
            lock (RefreshLock)
            {
                var list = (from pc in _possibleChannels.AsParallel()
                            where pc.MMEChannelType == (int)MMEPossibleChannels.MMEChannelTypes.SQL
                            select pc).ToArray();

                if (null != list && list.Any())
                {
                    return list.Select(li => new MMEPossibleChannels(li)).ToArray();
                }
                return new MMEPossibleChannels[0];
            }
        }

        public MMEPossibleChannels[] GetAllPossibleChannels()
        {
            lock (RefreshLock)
            {
                return _possibleChannels.ToArray();
            }
        }

        /// <summary>
        /// gets the "type" field from all possible channels where the channel test object matchines the input string in to
        /// </summary>
        /// <param name="to"></param>
        /// <returns></returns>
        public string[] GetTestObjectTypeForTestObject(string to)
        {
            RefreshIfNeeded();
            lock (RefreshLock)
            {
                return (from pc in _possibleChannels.AsParallel()
                        where pc.Test_Object == to
                        orderby pc.Type
                        select pc.Type).Distinct().ToArray();
            }
        }

        /// <summary>
        /// dictionary of possible channels keyed by id, there's a separate dictionary for iso13499 origined channels
        /// and DataPRO origined channels
        /// </summary>
        private Dictionary<long, MMEPossibleChannels> _mmePossibleChannelsDict = null;
        private Dictionary<long, MMEPossibleChannels> _mmePossibleChannelsDictOurs = null;
        public MMEPossibleChannels GetPossibleChannel(long id, int channelType)
        {
            RefreshIfNeeded();
            lock (RefreshLock)
            {
                if (1 == channelType)
                {
                    if (null == _mmePossibleChannelsDictOurs)
                    {
                        _mmePossibleChannelsDictOurs = new Dictionary<long, MMEPossibleChannels>();
                        foreach (var channel in _possibleChannels)
                        {
                            if (channel.MMEChannelType != channelType)
                            {
                                continue;
                            }
                            if (!_mmePossibleChannelsDictOurs.ContainsKey(channel.Id))
                            {
                                _mmePossibleChannelsDictOurs.Add(channel.Id, channel);
                            }
                        }
                    }
                    return _mmePossibleChannelsDictOurs.ContainsKey(id) ? _mmePossibleChannelsDictOurs[id] : null;
                }
                if (null == _mmePossibleChannelsDict)
                {
                    _mmePossibleChannelsDict = new Dictionary<long, MMEPossibleChannels>();
                    foreach (var channel in _possibleChannels)
                    {
                        if (channel.MMEChannelType != channelType)
                        {
                            continue;
                        }
                        if (!_mmePossibleChannelsDict.ContainsKey(channel.Id))
                        {
                            _mmePossibleChannelsDict.Add(channel.Id, channel);
                        }
                    }
                }
                return _mmePossibleChannelsDict.ContainsKey(id) ? _mmePossibleChannelsDict[id] : null;
            }
        }

        /// <summary>
        /// checks for an isocode in either ISO or our db
        /// </summary>
        /// <param name="isocode"></param>
        /// <returns></returns>
        public bool ContainsPossibleChannelWithISOCode(string isocode)
        {
            lock (RefreshLock)
            {
                return _mmePossibleChannelsDict.Any(ch => IsoCodeStatics.GetString(ch.Value, false) == isocode)
                       || _mmePossibleChannelsDictOurs.Any(ch => IsoCodeStatics.GetString(ch.Value, false) == isocode);
            }
        }

        /// <summary>
        /// gets all possible test objects from test object tables
        /// </summary>
        /// <param name="bIncludeExpired"></param>
        /// <returns></returns>
        public MMETestObjects[] GetTestObjects(bool bIncludeExpired)
        {
            RefreshIfNeeded();
            lock (RefreshLock)
            {
                return bIncludeExpired
                    ? _testObjectsDictionary.Values.ToArray()
                    : (from to in _testObjectsDictionary.Values.ToArray().AsParallel() where !to.Expired select to)
                    .ToArray();
            }
        }

        /// <summary>
        /// returns any test objects which match test object test object iso code.
        /// returns null if not found
        /// </summary>
        /// <param name="iso"></param>
        /// <param name="bIncludeExpired"></param>
        /// <returns></returns>
        public MMETestObjects GetTestObjectByIso(string iso, bool bIncludeExpired = true)
        {
            MMETestObjects testObjectType = null;

            RefreshIfNeeded();
            lock (RefreshLock)
            {
                if (bIncludeExpired)
                {
                    if (_testObjectsDictionary.ContainsKey(iso))
                    {
                        testObjectType = _testObjectsDictionary[iso];
                    }
                }
                else
                {
                    var nonExpiredTestObjects = _testObjectsDictionary.Values.Where(testObject => !testObject.Expired).ToList();
                    var firstMatch = nonExpiredTestObjects.Find(obj => obj.Test_Object == iso);
                    if (null != firstMatch) { testObjectType = firstMatch; }
                }
            }
            return testObjectType;
        }

        /// <summary>
        /// returns a position if any that has a matching iso code position field
        /// returns null if not found
        /// </summary>
        /// <param name="key">position code to look for</param>
        /// <returns></returns>
        public MMEPositions GetPositionByISO(string key)
        {
            RefreshIfNeeded();
            lock (RefreshLock)
            {
                return _positionsDictionary.ContainsKey(key) ? _positionsDictionary[key] : null;
            }
        }

        /// <summary>
        /// returns the first position with a guid which matches the requested guid
        /// returns null if not found
        /// </summary>
        /// <param name="GUID">guid to look for</param>
        /// <returns></returns>
        public MMEPositions GetPosition(string GUID)
        {
            RefreshIfNeeded();
            lock (RefreshLock)
            {
                var positions = (from p in _positionsDictionary.Values.ToArray().AsParallel()
                                 where p.S_GUID == GUID
                                 select p);
                if (positions.Any())
                {
                    return positions.First();
                }
                return null;
            }
        }

        /// <summary>
        /// gets all possible filter classes
        /// </summary>
        /// <returns></returns>
        public MMEFilterClasses[] GetFilterClasses()
        {
            RefreshIfNeeded();
            lock (RefreshLock)
            {
                return _filterClassesDictionary.Values.ToArray();
            }
        }
        /// <summary>
        /// returns filter class matching the iso code filter class field
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public MMEFilterClasses GetFilterClassByIso(string key)
        {
            RefreshIfNeeded();
            lock (RefreshLock)
            {
                return _filterClassesDictionary.ContainsKey(key) ? _filterClassesDictionary[key] : null;
            }
        }

        /// <summary>
        /// gets all possible directions
        /// </summary>
        /// <returns></returns>
        public MMEDirections[] GetDirections()
        {
            RefreshIfNeeded();
            lock (RefreshLock)
            {
                return _directionsDictionary.Values.ToArray();
            }
        }
        /// <summary>
        /// returns a direction given the isocode direction field
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public MMEDirections GetDirectionByIso(string key)
        {
            RefreshIfNeeded();
            lock (RefreshLock)
            {
                return _directionsDictionary.ContainsKey(key) ? _directionsDictionary[key] : null;
            }
        }

        /// <summary>
        /// returns a fine location 1, given a matching iso fine location field
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public MMEFineLocations1 GetFineLocation1ByIso(string key)
        {
            RefreshIfNeeded();
            lock (RefreshLock)
            {
                return _fineLoc1Dictionary.ContainsKey(key) ? _fineLoc1Dictionary[key] : null;
            }
        }

        /// <summary>
        /// returns a fine location 2 based on fine location 2 iso code field
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public MMEFineLocations2 GetFineLocation2ByIso(string key)
        {
            RefreshIfNeeded();
            lock (RefreshLock)
            {
                return _fineLoc2Dictionary.ContainsKey(key) ? _fineLoc2Dictionary[key] : null;
            }
        }

        /// <summary>
        /// gets fine location 3 based on fine location 3 isocode field
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public MMEFineLocations3 GetFineLocation3ByIso(string key)
        {
            RefreshIfNeeded();
            lock (RefreshLock)
            {
                return _fineLoc3Dictionary.ContainsKey(key) ? _fineLoc3Dictionary[key] : null;
            }
        }

        /// <summary>
        /// gets all possible physical dimensions
        /// </summary>
        /// <returns></returns>
        public MMEPhysicalDimensions[] GetPhysicalDimensions()
        {
            RefreshIfNeeded();
            lock (RefreshLock)
            {
                return _physicalDimensionsDictionary.Values.ToArray();
            }
        }
        /// <summary>
        /// gets physical dimension by isocode physical dimension field
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public MMEPhysicalDimensions GetPhysicalDimensionByIso(string key)
        {
            RefreshIfNeeded();
            lock (RefreshLock)
            {
                return _physicalDimensionsDictionary.ContainsKey(key) ? _physicalDimensionsDictionary[key] : null;
            }
        }

        /// <summary>
        /// gets all possible positions
        /// </summary>
        /// <returns></returns>
        public MMEPositions[] GetPositions()
        {
            RefreshIfNeeded();
            lock (RefreshLock)
            {
                return _positionsDictionary.Values.ToArray();
            }
        }

        readonly Dictionary<string, MMETransducerMainLocation> _expiredMainLocations = new Dictionary<string, MMETransducerMainLocation>();
        /// <summary>
        /// gets a main location given an isocode main location field
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public MMETransducerMainLocation GetMainLocationByIso(string key)
        {
            RefreshIfNeeded();
            if (_transducerMainLoc.ContainsKey(key)) { return _transducerMainLoc[key]; }
            if (_expiredMainLocations.ContainsKey(key)) { throw new ExpiredISOFieldException(_expiredMainLocations[key].Remarks); }
            return null;
        }

        /// <summary>
        /// writes a possible channel to the datapro db
        /// </summary>
        /// <param name="channel"></param>
        public void Commit(MMEPossibleChannels channel, bool bNotify)
        {
            RefreshIfNeeded();
            lock (RefreshLock)
            {
                if (null == _mmePossibleChannelsDictOurs)
                {
                    GetPossibleChannel(-1, (int)MMEPossibleChannels.MMEChannelTypes.SQL);
                }
                if (channel.MMEChannelType == (int)MMEPossibleChannels.MMEChannelTypes.SQL && channel.Id != -1)
                {
                    if (!_mmePossibleChannelsDictOurs.ContainsKey(channel.Id))
                    {
                        channel.Insert();
                        try
                        {
                            using (var sql = DbOperations.GetSQLCommand(true))
                            {
                                try
                                {
                                    sql.CommandType = System.Data.CommandType.Text;
                                    sql.CommandText = "SELECT ID FROM tblMMEPossibleChannels where TEXT_L1=@TextL1";
                                    DbOperations.CreateParam(sql, "@TextL1", System.Data.SqlDbType.NVarChar,
                                        channel.Text_L1);
                                    using (var ds = DbOperations.Connection.QueryDataSet(sql))
                                    {
                                        channel.SetId(Convert.ToInt64(ds.Tables[0].Rows[0]["ID"]));
                                    }
                                }
                                finally
                                {
                                    sql.Connection.Dispose();
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            APILogger.Log(ex);
                        }
                        _mmePossibleChannelsDictOurs[channel.Id] = channel;
                    }
                    else
                    {
                        //we might be incorrectly updating the wrong channel if this is from an import ...
                        channel.Commit();
                        _mmePossibleChannelsDictOurs[channel.Id] = channel;
                    }
                }
                else
                {
                    channel.Commit();
                }
            }
            if (bNotify)
            {
                RefreshAllData();
            }
        }

        private bool _bLoaded = false;

        public void UnloadAllData()
        {
            lock (RefreshLock)
            {
                _bLoaded = false;
                _mmePossibleChannelsDict?.Clear();
                _mmePossibleChannelsDict = null;
                _mmePossibleChannelsDictOurs?.Clear();
                _mmePossibleChannelsDictOurs = null;
                _uniquePossibleChannelTypes = null;
                _directionsDictionary?.Clear();
                _filterClassesDictionary?.Clear();
                _fineLoc1Dictionary?.Clear();
                _fineLoc2Dictionary?.Clear();
                _fineLoc3Dictionary?.Clear();
                _possibleChannelsByType?.Clear();
                _physicalDimensionsDictionary?.Clear();
                _positionsDictionary?.Clear();
                _possibleChannels?.Clear();
                _testObjectsDictionary?.Clear();
                _transducerMainLoc?.Clear();
                _expiredMainLocations?.Clear();
            }
        }
        private static readonly object RefreshLock = new object();

        private void ProcessPossibleChannels()
        {
            foreach (var pc in _possibleChannels)
            {
                //Don't include 2535, the Thoracic Compression Criterion channel which contains an expired main location (TCCR), but isn't itself marked as expired (FB 9891).
                if (pc.Id == 2535 ||
                    pc.Direction == "R" && !pc.Text_L1.ToLower().Contains("seat") && !pc.Text_L1.ToLower().Contains("load") ||
                    pc.Expired || pc.Default_Filter_Class == "V") { continue; }

                if (!_possibleChannelsByType.ContainsKey(pc.Type))
                {
                    _possibleChannelsByType[pc.Type] = new List<MMEPossibleChannels>();
                }
                _possibleChannelsByType[pc.Type].Add(pc);
            }
        }

        public bool CanConnect()
        {
            using (var cmd = DbOperations.GetISOCommand())
            {
                try
                {
                    cmd.CommandText = "SELECT * FROM MMEDirections";
                    using (var ISOReader = cmd.ExecuteReader())
                    {
                        while (ISOReader.Read())
                        {
                        }
                    }
                    return true;
                }
                catch (Exception ex)
                {
                    APILogger.Log(ex);
                    throw ex;
                }
                finally
                {
                    cmd.Connection.Dispose();
                }
            }
        }

        /// <summary>
        /// loads all data from iso13499 and datapro databases
        /// </summary>
        public void RefreshAllData()
        {
            lock (RefreshLock)
            {
                try
                {
                    _mmePossibleChannelsDict = new Dictionary<long, MMEPossibleChannels>();
                    _mmePossibleChannelsDictOurs = new Dictionary<long, MMEPossibleChannels>();
                    _uniquePossibleChannelTypes = null;
                    _directionsDictionary.Clear();
                    _filterClassesDictionary.Clear();
                    _fineLoc1Dictionary.Clear();
                    _fineLoc2Dictionary.Clear();
                    _fineLoc3Dictionary.Clear();
                    _possibleChannelsByType.Clear();
                    _physicalDimensionsDictionary.Clear();
                    _positionsDictionary.Clear();
                    _possibleChannels.Clear();
                    _testObjectsDictionary.Clear();
                    _transducerMainLoc.Clear();
                    _expiredMainLocations.Clear();

                    try
                    {
                        foreach (var dir in MMEDirections.GetDirections()) { if (!_directionsDictionary.ContainsKey(dir.Direction)) { _directionsDictionary.Add(dir.Direction, dir); } }

                        foreach (var fc in MMEFilterClasses.GetFilterClasses()) { if (!_filterClassesDictionary.ContainsKey(fc.Filter_Class)) { _filterClassesDictionary.Add(fc.Filter_Class, fc); } }

                        foreach (var loc in MMEFineLocations1.GetFineLocations1()) { if (!_fineLoc1Dictionary.ContainsKey(loc.Fine_Loc_1)) { _fineLoc1Dictionary.Add(loc.Fine_Loc_1, loc); } }

                        foreach (var loc in MMEFineLocations2.GetFineLocations2()) { if (!_fineLoc2Dictionary.ContainsKey(loc.FINE_LOC_2)) { _fineLoc2Dictionary.Add(loc.FINE_LOC_2, loc); } }

                        foreach (var loc in MMEFineLocations3.GetFineLocations3()) { if (!_fineLoc3Dictionary.ContainsKey(loc.FINE_LOC_3)) { _fineLoc3Dictionary.Add(loc.FINE_LOC_3, loc); } }

                        foreach (var pd in MMEPhysicalDimensions.GetPhysicalDimensions()) { if (!_physicalDimensionsDictionary.ContainsKey(pd.Physical_Dimension)) { _physicalDimensionsDictionary.Add(pd.Physical_Dimension, pd); } }

                        foreach (var pos in MMEPositions.GetPositions()) { if (!_positionsDictionary.ContainsKey(pos.Position)) { _positionsDictionary.Add(pos.Position, pos); } }

                        _possibleChannels.AddRange(MMEPossibleChannels.GetPossibleChannels());

                        ProcessPossibleChannels();

                        foreach (var to in MMETestObjects.GetTestObjects()) { if (!_testObjectsDictionary.ContainsKey(to.Test_Object)) { _testObjectsDictionary.Add(to.Test_Object, to); } }

                        foreach (var mainloc in MMETransducerMainLocation.GetTransducerMainLocations())
                        {
                            if (mainloc.Expired) { if (!_expiredMainLocations.ContainsKey(mainloc.Trans_Main_Loc)) { _expiredMainLocations.Add(mainloc.Trans_Main_Loc, mainloc); } }
                            else { if (!_transducerMainLoc.ContainsKey(mainloc.Trans_Main_Loc)) { _transducerMainLoc.Add(mainloc.Trans_Main_Loc, mainloc); } }
                        }

                        foreach (var pc in _possibleChannels)
                        {
                            if (pc.MMEChannelType == (int)MMEPossibleChannels.MMEChannelTypes.SQL) { _mmePossibleChannelsDictOurs[pc.Id] = pc; }
                            else { _mmePossibleChannelsDict[pc.Id] = pc; }
                        }
                    }
                    catch (Exception ex)
                    {
                        APILogger.Log("failure to load ISO tables", ex);
                    }
                    _bLoaded = true;
                }
                catch (Exception ex)
                {
                    _bLoaded = false;
                    APILogger.Log(ex);
                }
            }
        }
    }
}