using System;
using System.Reflection;
using System.Threading.Tasks;
using System.Web.Http;
using System.Data;
using System.Collections.Generic;
using System.Xml;
using System.IO;

using Newtonsoft.Json;

using Matrix42.Common;
using update4u.SPS.DataLayer;
using update4u.SPS.Security;

using C4IT.Logging;
using static C4IT.Logging.cLogManager;
using System.Web.Http.Controllers;
using C4IT.Matrix42.ServerInfo;
using System.Dynamic;
using C4IT.Licensing;
using System.ComponentModel;
using System.Linq;
using System.Globalization;
using System.Data.SqlClient;
using System.Net.Sockets;
using update4u.SPS.DataLayer.Command;
using update4u.SPS.DataLayer.Transaction;
using System.Net.Http;
using System.Net;
using System.Text;
using System.Net.Http.Headers;
using Matrix42.Pandora.Contracts.Action;

namespace C4IT.LIAM
{
    public static class cLiamM42Helper
    {
        public static dynamic ToDynamic(this object value)
        {
            IDictionary<string, object> expando = new ExpandoObject();

            foreach (PropertyDescriptor property in TypeDescriptor.GetProperties(value.GetType()))
                expando.Add(property.Name, property.GetValue(value));

            return expando as ExpandoObject;
        }
    }

    [RoutePrefix("api/C4ITLiamWebApi")]
    public class LiamM42WebApiController : ApiController
    {
        public const string LiamProductGuid = "28757DE2-690F-4D3B-9AB7-4AB7FB630901";

        public const string constFragmentNameDataAreaTargetPickupType = "C4IT_DataAreaTargetPickupType";

        public const string constFragmentNameDataAreaMain = "C4IT_DataAreaClassBase";
        public const string constFragmentNameConfigProviderMain = "C4IT_GCC_DataArea_Collector";
        public const string constFragmentNameAddonConfigClassBase = "C4IT_AddonConfigClassBase";
        public const string constFragmentNameConfigProviderBase = "SPSGenericConnectorConfigurationClassBase";
        public const string constFragmentNameConfigProviderAdditionalAttributes = "C4IT_GCC_DataArea_Collector_AdditionalAttributes";
        public const string constFragmentNameCustomTagBase = "C4IT_DataAreaCustomTagClassBase";
        public const string constFragmentNameNamingConvention = "C4IT_LIAMNamingConvention";
        public const string constFragmentNameConfigNamingConvention = "C4IT_LIAMConfigNamingConvention";
        public const string constTypeNameConfigProvider = "C4IT_GCC_DataAreaCollectorType";
        public const string constFragmentNameAccountAd = "SPSAccountClassAD";
        public const string constFragmentNameAccountBase = "SPSAccountClassBase";
        public const string constFragmentNameUserBase = "SPSUserClassBase";
        public const string constFragmentNameCommon = "SPSCommonClassBase";
        public const string constFragmentNameSVCServiceBookingClassBase = "SVCServiceBookingClassBase";
        public const string sqlCSVBookingData = @"
select 
    da.technicalName as TechnicalName,                  -- TechnicalName
    s.Name as ServiceName,             -- ServiceName
    u.LastName + ', ' + u.FirstName as ConsumerName, -- ConsumerName
	a.NBAccountName as AccountName,
    s.ArticleID,                       -- ArticleID
    o.TicketNumber as OrderNumber,     -- OrderNumber
    b.BookingID,                       -- BookingID
    b.ProvisionedDate,                 -- ProvisionedDate
    da.ID as ID_noexport,               -- Technisch notwendig, wird aber nicht exportiert
	b.Uninstalled as Deprovisioned,
	bu.ProvisionedDate as DeProvisioningDate
from SVCServiceBookingClassBase b WITH (NOLOCK)
left join SPSSelfServiceOrderItemClassBase o WITH (NOLOCK) on o.ID = b.[Order]
left join SVCServiceBookingClassBase bu on bu.InstallationBooking = b.ID
left join SPSArticleClassBase s WITH (NOLOCK) on b.[Service] = s.ID
left join SPSCommonClassBase sf WITH (NOLOCK) on s.ServiceForm = sf.ID
left join C4IT_DataAreaServiceConfigurationClassBase sc WITH (NOLOCK) on sc.[Expression-ObjectID] = sf.[Expression-ObjectID]
left join C4IT_DataAreaSecurityGroupClassBase sg WITH (NOLOCK) on sc.SecurityGroup = sg.ID
left join C4IT_DataAreaClassBase da WITH (NOLOCK) on (da.[Read] = sg.ID OR da.Write = sg.ID OR da.[Owner] = sg.ID)
left join SPSUserClassBase u WITH (NOLOCK) on b.Consumer = u.ID
left join SPSTargetingClassTarget t on b.[Target] = t.ID
left join SPSAccountClassAD a on a.[Expression-ObjectID] = t.[Expression-ObjectID]
where ";


        private static readonly Dictionary<Guid, ProviderCacheEntry> ProviderCacheByID = new Dictionary<Guid, ProviderCacheEntry>();
        private static readonly Dictionary<Guid, ProviderCacheEntry> ProviderCacheByObjectID = new Dictionary<Guid, ProviderCacheEntry>();

        public static bool IsInitialized { get; private set; } = false;

        private static readonly object initLock = new object();

        protected override void Initialize(HttpControllerContext controllerContext)
        {
            base.Initialize(controllerContext);
            try
            {
                if (IsInitialized)
                    return;

                lock (initLock)
                {
                    if (IsInitialized)
                        return;

                    var Ass = Assembly.GetExecutingAssembly();
                    var LM = cLogManagerFile.CreateInstance(LocalMachine: true, A: Ass);
                    var CM = MethodBase.GetCurrentMethod();
                    LogMethodBegin(CM);
                    cLogManager.DefaultLogger.LogAssemblyInfo(Ass);
                    IsInitialized = true;
                    LogMethodEnd(CM);
                }
            }
            catch { }
        }

        [HttpGet]
        [Route("loadLicense")]
        public bool LoadLicensingInformation(bool force = false)
        {
            if (force)
                cC4ITLicenseM42ESM.Instance = null;

            if (cC4ITLicenseM42ESM.Instance == null)
            {
                new cC4ITLicenseM42ESM(new Guid(LiamProductGuid));
                var strLicense = getLicenseString();
                if (!string.IsNullOrEmpty(strLicense))
                    cC4ITLicenseM42ESM.Instance.LoadFromString(strLicense);
            }
            return cC4ITLicenseM42ESM.Instance.IsValid;
        }

        private string getLicenseString()
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);
            try
            {
                var ClassID = SPSDataEngineSchemaReader.ClassGetIDFromName(constFragmentNameAddonConfigClassBase);
                LogEntry($"Addon Config class ID: {ClassID}", LogLevels.Debug);
                var dataTable = FragmentRequest.SimpleLoad(ClassID, "license", "UsedInTypeC4IT_LIAMConfigurationType is not null");
                if (dataTable?.Rows == null || dataTable.Rows.Count <= 0)
                {
                    LogEntry($"Addon Config fragment not found: ClassID={ClassID}", LogLevels.Debug);
                    return null;
                }
                var license = cLIAMHelper.getStringFromObject(dataTable.Rows[0]["license"]);
                LogEntry($"Addon Config license: {license}", LogLevels.Debug);
                return license;
            }
            catch (Exception E)
            {
                LogException(E);
                return null;
            }
            finally
            {
                LogMethodEnd(CM);
            }
        }
        // Neue Methode
        [HttpPost]
        [Route("getCSVBookingData")]
        public HttpResponseMessage getCSVBookingData(ActionExecuteData actionExecuteData)
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);
            var csvBuilder = new StringBuilder();
            string serviceNameForFile = string.Empty;
            var distinctDataAreaIds = new HashSet<Guid>();
            try
            {
                if (actionExecuteData?.Objects == null || actionExecuteData.Objects.Length == 0)
                {
                    return new HttpResponseMessage(HttpStatusCode.BadRequest)
                    {
                        Content = new StringContent("Keine ObjectIds übergeben.")
                    };
                }
                List<Guid> dataAreaIds = new List<Guid>();
                foreach (ActionExecuteDataEntry entry in actionExecuteData.Objects)
                {
                    if (entry.ObjectIds != null)
                    {
                        dataAreaIds.AddRange(entry.ObjectIds);
                    }
                }
                if (dataAreaIds.Count == 0)
                {
                    return new HttpResponseMessage(HttpStatusCode.BadRequest)
                    {
                        Content = new StringContent("Keine gültigen ObjectIds gefunden.")
                    };
                }
                var parameterNames = new List<string>();
                for (int i = 0; i < dataAreaIds.Count; i++)
                {
                    parameterNames.Add($"@dataareaid{i}");
                }
                string whereClause = "da.[Expression-ObjectId] IN (" + string.Join(", ", parameterNames) + ")";
                string sqlQuery = sqlCSVBookingData + whereClause;
                using (var sqlCommand = new SqlCommand(sqlQuery))
                {
                    for (int i = 0; i < dataAreaIds.Count; i++)
                    {
                        sqlCommand.Parameters.AddWithValue($"@dataareaid{i}", dataAreaIds[i]);
                    }
                    using (var sPSTransactionScope = new SPSTransactionScope()) using (IDataReader dataReader = DirectDbCommand.ExecuteReader(sqlCommand))
                    { // Spaltennamen ermitteln, Spalten mit '_noexport' ausschließen 
                        var columnNames = new List<string>();
                        for (int i = 0; i < dataReader.FieldCount; i++)
                        {
                            string columnName = dataReader.GetName(i);
                            if (!columnName.EndsWith("_noexport"))
                            {
                                columnNames.Add(columnName);
                            }
                        } // CSV-Header erstellen 
                        csvBuilder.AppendLine("sep=;");
                        csvBuilder.AppendLine(string.Join(";", columnNames));
                        bool isFirstRow = true;
                        while (dataReader.Read())
                        { // Zugriff auf 'ID_noexport' für interne Logik, ohne Export 
                            if (dataReader["ID_noexport"] != DBNull.Value)
                            {
                                distinctDataAreaIds.Add((Guid)dataReader["ID_noexport"]);
                            }
                            if (isFirstRow)
                            {
                                if (dataReader["ServiceName"] != DBNull.Value)
                                {
                                    serviceNameForFile = dataReader["ServiceName"].ToString();
                                }
                                isFirstRow = false;
                            } // Werte nur der exportierten Spalten sammeln 
                            var rowValues = new List<string>();
                            foreach (var columnName in columnNames)
                            {
                                var value = dataReader[columnName];
                                var stringValue = value == DBNull.Value ? string.Empty : value.ToString();
                                rowValues.Add(EscapeForCsv(stringValue));
                            }
                            csvBuilder.AppendLine(string.Join(";", rowValues));
                        }
                        sPSTransactionScope.Complete();
                    }
                }
            }
            catch (Exception ex)
            {
                LogException(ex);
                return new HttpResponseMessage(HttpStatusCode.InternalServerError)
                {
                    Content = new StringContent("Fehler bei der Verarbeitung der Anfrage.")
                };
            }
            finally
            {
                LogMethodEnd(CM);
            } // Dateinamen erstellen 
            string fileName;
            if (distinctDataAreaIds.Count == 1)
            {
                int index = serviceNameForFile.IndexOf('[');
                if (index > 0)
                {
                    serviceNameForFile = serviceNameForFile.Substring(0, index).TrimEnd(' ', '-');
                }
                var safeServiceName = CleanFileName(serviceNameForFile);
                fileName = $"Servicebuchungen_{safeServiceName}_{DateTime.Now:yyyyMMdd}.csv";
            }
            else
            {
                fileName = $"Servicebuchungen_{DateTime.Now:yyyyMMdd}.csv";
            }
            var response = new HttpResponseMessage(HttpStatusCode.OK)
            {
                Content = new StringContent(csvBuilder.ToString(), Encoding.UTF8, "text/csv")
            };
            response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
            {
                FileName = fileName
            };
            return response;
        }
        /* Neue Methode
                [HttpPost]
                [Route("getCSVBookingData")]
                public HttpResponseMessage getCSVBookingData(ActionExecuteData actionExecuteData)
                {
                    var CM = MethodBase.GetCurrentMethod();
                    LogMethodBegin(CM);

                    var csvBuilder = new StringBuilder();
                    string serviceNameForFile = string.Empty;
                    var distinctDataAreaIds = new HashSet<Guid>();

                    try
                    {
                        if (actionExecuteData?.Objects == null || actionExecuteData.Objects.Length == 0)
                        {
                            return new HttpResponseMessage(HttpStatusCode.BadRequest)
                            {
                                Content = new StringContent("Keine ObjectIds übergeben.")
                            };
                        }

                        List<Guid> dataAreaIds = new List<Guid>();
                        foreach (ActionExecuteDataEntry entry in actionExecuteData.Objects)
                        {
                            if (entry.ObjectIds != null)
                            {
                                dataAreaIds.AddRange(entry.ObjectIds);
                            }
                        }

                        if (dataAreaIds.Count == 0)
                        {
                            return new HttpResponseMessage(HttpStatusCode.BadRequest)
                            {
                                Content = new StringContent("Keine gültigen ObjectIds gefunden.")
                            };
                        }

                        var parameterNames = new List<string>();
                        for (int i = 0; i < dataAreaIds.Count; i++)
                        {
                            parameterNames.Add($"@dataareaid{i}");
                        }
                        string whereClause = "da.[Expression-ObjectID] IN (" + string.Join(", ", parameterNames) + ")";
                        string sqlQuery = sqlCSVBookingData + whereClause;

                        // CSV-Aufbau
                        var columnNames = new List<string>();
                        bool headerWritten = false;
                        bool firstRow = true;

                        DirectDbCommand.ExecuteReader(
                            dbCmd =>
                            {
                                dbCmd.CommandText = sqlQuery;

                                for (int i = 0; i < dataAreaIds.Count; i++)
                                {
                                    var p = dbCmd.CreateParameter();
                                    p.ParameterName = $"@dataareaid{i}";
                                    p.DbType = DbType.Guid;
                                    p.Value = dataAreaIds[i];
                                    dbCmd.Parameters.Add(p);
                                }
                            },
                            reader =>
                            {
                                // Header einmalig aus dem ersten gelesenen Datensatz ableiten
                                if (!headerWritten)
                                {
                                    for (int i = 0; i < reader.FieldCount; i++)
                                    {
                                        var name = reader.GetName(i);
                                        if (!name.EndsWith("_noexport", StringComparison.OrdinalIgnoreCase))
                                            columnNames.Add(name);
                                    }

                                    csvBuilder.AppendLine("sep=;");
                                    csvBuilder.AppendLine(string.Join(";", columnNames));
                                    headerWritten = true;
                                }

                                // interne Logik mit ID_noexport (wird nicht exportiert)
                                int idxNoExport = reader.GetOrdinal("ID_noexport");
                                if (!reader.IsDBNull(idxNoExport))
                                    distinctDataAreaIds.Add(reader.GetGuid(idxNoExport));

                                // Dateiname: ServiceName aus erster Zeile merken
                                if (firstRow)
                                {
                                    int idxServiceName = reader.GetOrdinal("ServiceName");
                                    if (!reader.IsDBNull(idxServiceName))
                                        serviceNameForFile = reader.GetString(idxServiceName);
                                    firstRow = false;
                                }

                                // Werte der exportierten Spalten schreiben
                                var rowValues = new List<string>(columnNames.Count);
                                foreach (var col in columnNames)
                                {
                                    var val = reader[col];
                                    rowValues.Add(EscapeForCsv(val == DBNull.Value ? string.Empty : Convert.ToString(val)));
                                }
                                csvBuilder.AppendLine(string.Join(";", rowValues));
                            }
                        );
                    }
                    catch (Exception ex)
                    {
                        LogException(ex);
                        return new HttpResponseMessage(HttpStatusCode.InternalServerError)
                        {
                            Content = new StringContent("Fehler bei der Verarbeitung der Anfrage.")
                        };
                    }
                    finally
                    {
                        LogMethodEnd(CM);
                    }

                    // Dateinamen erstellen
                    string fileName;
                    if (distinctDataAreaIds.Count == 1)
                    {
                        int index = serviceNameForFile.IndexOf('[');
                        if (index > 0)
                        {
                            serviceNameForFile = serviceNameForFile.Substring(0, index).TrimEnd(' ', '-');
                        }
                        var safeServiceName = CleanFileName(serviceNameForFile);
                        fileName = $"Servicebuchungen_{safeServiceName}_{DateTime.Now:yyyyMMdd}.csv";
                    }
                    else
                    {
                        fileName = $"Servicebuchungen_{DateTime.Now:yyyyMMdd}.csv";
                    }

                    var response = new HttpResponseMessage(HttpStatusCode.OK)
                    {
                        Content = new StringContent(csvBuilder.ToString(), Encoding.UTF8, "text/csv")
                    };

                    response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
                    {
                        FileName = fileName
                    };

                    return response;
                }
        */

        // Hilfsmethoden
        private string EscapeForCsv(string field)
        {
            if (field.Contains(";") || field.Contains("\"") || field.Contains("\n") || field.Contains("\r"))
            {
                field = field.Replace("\"", "\"\"");
                field = $"\"{field}\"";
            }
            return field;
        }

        private string CleanFileName(string fileName)
        {
            foreach (char c in Path.GetInvalidFileNameChars())
            {
                fileName = fileName.Replace(c.ToString(), string.Empty);
            }
            return fileName;
        }


        private cLiamProviderBase CreateProviderInstance(cLiamConfiguration LiamConfiguration, cLiamProviderData ProviderData)
        {
            LoadLicensingInformation();
            ProviderData.ReplaceCustomTags();
            switch (ProviderData.ProviderType)
            {
                case eLiamProviderTypes.MsTeams:
                    return new cLiamProviderMsTeams(LiamConfiguration, ProviderData);
                case eLiamProviderTypes.Ntfs:
                    return new cLiamProviderNtfs(LiamConfiguration, ProviderData);
                case eLiamProviderTypes.ActiveDirectory:
                    return new cLiamProviderAD(LiamConfiguration, ProviderData);
            }
            return null;
        }

        private static ProviderCacheEntry AddCache(Guid ID, Guid ObjectID, cLiamProviderBase Provider)
        {
            var Entry = new ProviderCacheEntry()
            {
                ID = ID,
                ObjectID = ObjectID,
                ValidUntil = DateTime.UtcNow + TimeSpan.FromMinutes(15),
                Provider = Provider
            };

            ProviderCacheByID[ID] = Entry;
            ProviderCacheByObjectID[ObjectID] = Entry;

            return Entry;
        }

        private static ProviderCacheEntry GetCacheByID(Guid ID)
        {
            if (!ProviderCacheByID.TryGetValue(ID, out var Entry))
                return null;

            if (Entry.ValidUntil < DateTime.UtcNow)
            {
                ProviderCacheByID.Remove(ID);
                ProviderCacheByObjectID.Remove(Entry.ObjectID);
                return null;
            }

            return Entry;
        }

        private static ProviderCacheEntry GetCacheByObjectID(Guid ObjectID)
        {
            if (!ProviderCacheByObjectID.TryGetValue(ObjectID, out var Entry))
                return null;

            if (Entry.ValidUntil < DateTime.UtcNow)
            {
                ProviderCacheByID.Remove(Entry.ID);
                ProviderCacheByObjectID.Remove(ObjectID);
                return null;
            }

            return Entry;
        }

        [Route("getVersionInfo"), HttpGet]
        public LiamApiVersionInfo getVersionInfo()
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);
            var RetVal = new LiamApiVersionInfo();
            try
            {
                var A = Assembly.GetExecutingAssembly();
                RetVal.ProductVersion = A.GetCustomAttribute<AssemblyInformationalVersionAttribute>()?.InformationalVersion;
                RetVal.AssemblyVersion = A.GetName().Version.ToString();
                RetVal.ProductName = A.GetCustomAttribute<AssemblyProductAttribute>()?.Product;
                RetVal.AssemblyName = A.ManifestModule.ToString();
            }
            catch (Exception E)
            {
                LogException(E);
            }
            finally
            {
                LogMethodEnd(CM);
            }
            return RetVal;
        }

        private bool TryLoadProviderFragments(Guid providerConfigClassID, out Guid objectId, out DataTable tblMain, out DataTable tblBase, out DataTable tblAdditionalAttr, out DataTable tblNamingConvention, out DataTable tblCustomTags)
        {
            objectId = Guid.Empty;
            tblMain = null;
            tblBase = null;
            tblAdditionalAttr = null;
            tblNamingConvention = null;
            tblCustomTags = null;

            try
            {
                var classID = SPSDataEngineSchemaReader.ClassGetIDFromName(constFragmentNameConfigProviderMain);
                LogEntry($"Config provider class ID: {classID}", LogLevels.Debug);
                var fragment = FragmentRequest.GetSPSFragment(classID, providerConfigClassID);
                if (fragment == null)
                {
                    LogEntry($"Provider config fragment not found: ClassID={classID}, FragmentId={providerConfigClassID}", LogLevels.Debug);
                    return false;
                }

                objectId = fragment.ObjectID;
                if (objectId == Guid.Empty)
                {
                    LogEntry($"Provider config fragment not found: ClassID={classID}, FragmentId={providerConfigClassID}", LogLevels.Debug);
                    return false;
                }

                tblMain = fragment.FragmentTable;
                LogEntry($"Config provider object ID: {objectId}", LogLevels.Debug);

                classID = SPSDataEngineSchemaReader.ClassGetIDFromName(constFragmentNameConfigProviderBase);
                LogEntry($"Config provider base class ID: {classID}", LogLevels.Debug);

                Guid[] ids = { objectId };
                tblBase = FragmentRequestBase.SimpleLoad(classID,
                    "Account, Password",
                    AsqlHelper.BuildInCondition("[Expression-ObjectID]", ids));
                if (tblBase?.Rows == null || tblBase.Rows.Count <= 0)
                {
                    LogEntry($"Provider config base fragment not found: ClassId={classID}, ObjectId={objectId}", LogLevels.Debug);
                    return false;
                }

                classID = SPSDataEngineSchemaReader.ClassGetIDFromName(constFragmentNameConfigProviderAdditionalAttributes);
                LogEntry($"Config provider additional config class ID: {classID}", LogLevels.Debug);
                tblAdditionalAttr = FragmentRequestBase.SimpleLoad(classID,
                    "Name, Value",
                    AsqlHelper.BuildInCondition("[Expression-ObjectID]", ids));
                if (tblAdditionalAttr == null)
                {
                    LogEntry($"Provider additional config class fragment not found: ClassId={classID}, ObjectId={objectId}", LogLevels.Debug);
                    return false;
                }

                classID = SPSDataEngineSchemaReader.ClassGetIDFromName(constFragmentNameCustomTagBase);
                LogEntry($"Custom Tag class ID: {classID}", LogLevels.Debug);
                tblCustomTags = FragmentRequestBase.SimpleLoad(classID, "Key, Name", $"[Expression-ObjectID]='{objectId}'") ?? new DataTable();

                classID = SPSDataEngineSchemaReader.ClassGetIDFromName(constFragmentNameConfigNamingConvention);
                LogEntry($"Naming convention config class ID: {classID}", LogLevels.Debug);
                tblNamingConvention = FragmentRequestBase.SimpleLoad(classID,
                    "ID, Usage, NamingConvention.Description as Description, NamingConvention.DescriptionTemplate as DescriptionTemplate, " +
                    "NamingConvention.Name as Name, NamingConvention.NamingTemplate as NamingTemplate, NamingConvention.Wildcard as Wildcard",
                    $"[Expression-ObjectID]='{objectId}'") ?? new DataTable();

                return true;
            }
            catch (Exception e)
            {
                LogException(e);
                return false;
            }
        }

        private cLiamProviderData buildProviderData(DataTable dtMain, DataTable dtBase, DataTable dtAdditional, DataTable dtNamingConvention, DataTable dtCustomTag, bool includeSecret, out string sanitizedJson)
        {
            sanitizedJson = null;
            if (dtMain?.Rows == null || dtMain.Rows.Count <= 0)
                return null;
            if (dtBase?.Rows == null || dtBase.Rows.Count <= 0)
                return null;
            if (dtAdditional == null)
                return null;

            var dataMain = dtMain.Rows[0];
            var dataBase = dtBase.Rows[0];

            var encPW = cLIAMHelper.getStringFromObject(dataBase["Password"]);
            if (!CryptoManager.Instance.TryDecryptDBText(encPW, out string password))
                password = encPW;

            var providerData = new cLiamProviderData()
            {
                Domain = cLIAMHelper.getStringFromObject(dataMain["GCCDomain"]),
                Credential = new cLiamCredential()
                {
                    Domain = cLIAMHelper.getStringFromObject(dataMain["GCCDomain"]),
                    Identification = cLIAMHelper.getStringFromObject(dataBase["Account"]),
                    Secret = "***"
                },
                RootPath = cLIAMHelper.getStringFromObject(dataMain["GCCTarget"]),
                MaxDepth = cLIAMHelper.getIntFromObject(dataMain["GCCMaxDepth"]),
                GroupFilter = cLIAMHelper.getStringFromObject(dataMain["GCCgroupLDAPFilter"]),
                GroupPath = cLIAMHelper.getStringFromObject(dataMain["GCCgroupOUPath"]),
                GroupStrategy = (eLiamGroupStrategies)cLIAMHelper.getIntFromObject(dataMain["GCCPermissionGroupStrategy"]),
                ProviderType = (eLiamProviderTypes)cLIAMHelper.getIntFromObject(dataMain["GCCtargetType"])
            };

            if (dtAdditional?.Rows != null)
            {
                foreach (DataRow row in dtAdditional.Rows)
                {
                    var name = cLIAMHelper.getStringFromObject(row["Name"]);
                    var value = cLIAMHelper.getStringFromObject(row["Value"]);
                    if (!string.IsNullOrEmpty(name))
                        providerData.AdditionalConfiguration[name] = value;
                }
            }

            if (dtCustomTag?.Rows != null)
            {
                foreach (DataRow row in dtCustomTag.Rows)
                {
                    var name = cLIAMHelper.getStringFromObject(row["Key"]);
                    var value = cLIAMHelper.getStringFromObject(row["Name"]);
                    if (!string.IsNullOrEmpty(name))
                        providerData.CustomTags[name] = value;
                }
            }

            if (dtNamingConvention?.Rows != null)
            {
                foreach (DataRow row in dtNamingConvention.Rows)
                {
                    var usage = cLIAMHelper.getIntFromObject(row["Usage"]);
                    var accessRole = eLiamAccessRoles.Read;
                    var scope = eLiamAccessRoleScopes.Unknown;
                    switch (usage)
                    {
                        case -10:
                            accessRole = eLiamAccessRoles.Traverse;
                            scope = eLiamAccessRoleScopes.Global;
                            break;
                        case 10:
                            accessRole = eLiamAccessRoles.Read;
                            scope = eLiamAccessRoleScopes.Global;
                            break;
                        case 20:
                            accessRole = eLiamAccessRoles.Write;
                            scope = eLiamAccessRoleScopes.Global;
                            break;
                        case 30:
                            accessRole = eLiamAccessRoles.Owner;
                            scope = eLiamAccessRoleScopes.Global;
                            break;
                        case 40:
                            accessRole = eLiamAccessRoles.Read;
                            scope = eLiamAccessRoleScopes.DomainLocal;
                            break;
                        case 50:
                            accessRole = eLiamAccessRoles.Write;
                            scope = eLiamAccessRoleScopes.DomainLocal;
                            break;
                        case 60:
                            accessRole = eLiamAccessRoles.Owner;
                            scope = eLiamAccessRoleScopes.DomainLocal;
                            break;
                        case 100:
                            accessRole = eLiamAccessRoles.ADOwner;
                            scope = eLiamAccessRoleScopes.Global;
                            break;
                        case 110:
                            accessRole = eLiamAccessRoles.ADMember;
                            scope = eLiamAccessRoleScopes.Global;
                            break;
                        case 200:
                            accessRole = eLiamAccessRoles.ExchangeMLMember;
                            scope = eLiamAccessRoleScopes.Universal;
                            break;
                        case 210:
                            accessRole = eLiamAccessRoles.ExchangeMLOwner;
                            scope = eLiamAccessRoleScopes.Universal;
                            break;
                        case 250:
                            accessRole = eLiamAccessRoles.ExchangeSMBFullAccess;
                            scope = eLiamAccessRoleScopes.Universal;
                            break;
                        case 260:
                            accessRole = eLiamAccessRoles.ExchangeSMBSendAs;
                            scope = eLiamAccessRoleScopes.Universal;
                            break;
                        case 270:
                            accessRole = eLiamAccessRoles.ExchangeSMBOwner;
                            scope = eLiamAccessRoleScopes.Universal;
                            break;
                    }

                    providerData.NamingConventions.Add(new cLiamNamingConvention()
                    {
                        AccessRole = accessRole,
                        Scope = scope,
                        Description = cLIAMHelper.getStringFromObject(row["Description"]),
                        DescriptionTemplate = cLIAMHelper.getStringFromObject(row["DescriptionTemplate"]),
                        Name = cLIAMHelper.getStringFromObject(row["Name"]),
                        NamingTemplate = cLIAMHelper.getStringFromObject(row["NamingTemplate"]),
                        Wildcard = cLIAMHelper.getStringFromObject(row["Wildcard"])
                    });
                }
            }

            sanitizedJson = JsonConvert.SerializeObject(providerData, Newtonsoft.Json.Formatting.Indented);

            if (includeSecret && providerData.Credential != null)
                providerData.Credential.Secret = password;

            return providerData;
        }

        private cLiamProviderBase createProvider(DataTable dtMain, DataTable dtBase, DataTable dtAdditional, DataTable dtNamingConvention = null, DataTable dtCustomTag = null)
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);
            try
            {
                var DataProviderData = buildProviderData(dtMain, dtBase, dtAdditional, dtNamingConvention, dtCustomTag, includeSecret: true, out var sanitizedJson);
                if (DataProviderData == null)
                    return null;

                LogEntry("Provider configuration (sanitized JSON, copy for diagnostics tool):", LogLevels.Info);
                LogEntry(sanitizedJson, LogLevels.Info);

                var DataProvider = CreateProviderInstance(new cLiamConfiguration(), DataProviderData);

                return DataProvider;
            }
            catch (Exception E)
            {
                LogException(E);
                return null;
            }
            finally
            {
                LogMethodEnd(CM);
            }
        }

        [Route("initializeDataProvider"), HttpGet]
        public async Task<bool> initializeDataProvider(Guid ProviderConfigClassID, bool force = false)
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);
            try
            {
                if (cC4ITLicenseM42ESM.Instance == null)
                    LoadLicensingInformation();

                if (!cC4ITLicenseM42ESM.Instance.IsValid)
                {
                    LogEntry($"Error: License not valid", LogLevels.Error);
                    return false;
                }
                var DataProvider = await getDataProvider(ProviderConfigClassID, force);
                return DataProvider != null;
            }
            catch (Exception E)
            {
                LogException(E);
                return false;
            }
            finally
            {
                LogMethodEnd(CM);
            }
        }

        [Route("exportProviderConfiguration"), HttpGet]
        public IHttpActionResult exportProviderConfiguration(Guid ProviderConfigClassID)
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);
            try
            {
                if (cC4ITLicenseM42ESM.Instance == null)
                    LoadLicensingInformation();

                if (!cC4ITLicenseM42ESM.Instance.IsValid)
                {
                    LogEntry($"Error: License not valid", LogLevels.Error);
                    return Content(HttpStatusCode.Forbidden, "License not valid");
                }

                if (!TryLoadProviderFragments(ProviderConfigClassID, out var objectId, out var tblMain, out var tblBase, out var tblAdditionalAttr, out var tblNamingConvention, out var tblCustomTags))
                {
                    return Content(HttpStatusCode.NotFound, $"Provider configuration '{ProviderConfigClassID}' not found.");
                }

                var providerData = buildProviderData(tblMain, tblBase, tblAdditionalAttr, tblNamingConvention, tblCustomTags, includeSecret: false, out var sanitizedJson);
                if (providerData == null)
                    return Content(HttpStatusCode.InternalServerError, "Provider configuration could not be loaded.");

                var export = new ProviderConfigurationExport
                {
                    ProviderConfigClassID = ProviderConfigClassID,
                    ProviderConfigObjectID = objectId,
                    SanitizedJson = sanitizedJson,
                    Configuration = providerData,
                    GeneratedAtUtc = DateTime.UtcNow
                };

                return Ok(export);
            }
            catch (Exception E)
            {
                LogException(E);
                return InternalServerError(E);
            }
            finally
            {
                LogMethodEnd(CM);
            }
        }

        private async Task<ProviderCacheEntry> getDataProvider(Guid ProviderConfigClassID, bool force = false)
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);
            try
            {
                if (!force)
                {
                    var Provider = GetCacheByID(ProviderConfigClassID);
                    if (Provider != null)
                    {
                        LogEntry($"provider already cached", LogLevels.Debug);
                        return Provider;
                    }
                }
                if (!TryLoadProviderFragments(ProviderConfigClassID, out var ObjectID, out var tblMain, out var tblBase, out var tblAdditionalAttr, out var tblNamingConvention, out var tblCustomTags))
                    return null;

                var DataProvider = createProvider(tblMain, tblBase, tblAdditionalAttr, tblNamingConvention, tblCustomTags);
                if (DataProvider == null)
                {
                    LogEntry($"Provider configuration '{ProviderConfigClassID}' could not be materialized.", LogLevels.Warning);
                    return null;
                }

                var validLogon = await DataProvider.LogonAsync();
                if (!validLogon)
                    return null;

                var RetVal = AddCache(ProviderConfigClassID, ObjectID, DataProvider);

                return RetVal;
            }
            catch (Exception E)
            {
                LogException(E);
                return null;
            }
            finally
            {
                LogMethodEnd(CM);
            }
        }

        [Route("initializeDataProviderByObjectID"), HttpGet]
        public async Task<Guid> initializeDataProviderByObjectID(Guid ObjectID)
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);
            try
            {
                if (cC4ITLicenseM42ESM.Instance == null)
                    LoadLicensingInformation();

                if (!cC4ITLicenseM42ESM.Instance.IsValid)
                {
                    LogEntry($"Error: License not valid", LogLevels.Error);
                    return Guid.Empty;
                }
                var Provider = GetCacheByObjectID(ObjectID);
                if (Provider != null)
                {
                    LogEntry($"provider already cached", LogLevels.Debug);
                    return Provider.ID;
                }

                var TypeID = SPSDataEngineSchemaReader.TypeGetIDFromName(constTypeNameConfigProvider);
                LogEntry($"Config provider class ID: {TypeID}", LogLevels.Debug);
                SPSObject spsObject = ObjectRequest.GetSPSObject(TypeID, ObjectID);
                if (spsObject == null)
                {
                    LogEntry($"Provider config type not found: TypeID={TypeID}, ObjectID={ObjectID}", LogLevels.Debug);
                    return Guid.Empty;
                }

                var tblMain = spsObject.ClassTable[constFragmentNameConfigProviderMain];
                if (tblMain == null)
                {
                    LogEntry($"Provider config class not found: Type={constFragmentNameConfigProviderMain}, ObjectID={ObjectID}", LogLevels.Debug);
                    return Guid.Empty;
                }
                var tblBase = spsObject.ClassTable[constFragmentNameConfigProviderBase];
                if (tblBase == null)
                {
                    LogEntry($"Provider config class not found: Type={constFragmentNameConfigProviderBase}, ObjectID={ObjectID}", LogLevels.Debug);
                    return Guid.Empty;
                }
                var tblAdditionalAttributes = spsObject.ClassTable[constFragmentNameConfigProviderAdditionalAttributes];
                if (tblAdditionalAttributes == null)
                {
                    LogEntry($"Provider additional config class not found: Type={constFragmentNameConfigProviderAdditionalAttributes}, ObjectID={ObjectID}", LogLevels.Debug);
                    return Guid.Empty;
                }

                var DataProvider = createProvider(tblMain, tblBase, tblAdditionalAttributes);

                var RetVal = await DataProvider.LogonAsync();
                if (!RetVal)
                    return Guid.Empty;

                var ID = cLIAMHelper.getGuidFromObject(tblMain.Rows[0]["ID"]);

                AddCache(ID, ObjectID, DataProvider);

                return ID;
            }
            catch (Exception E)
            {
                LogException(E);
                return Guid.Empty;
            }
            finally
            {
                LogMethodEnd(CM);
            }
        }



        [Route("getDataAreasOwner"), HttpGet]
        public async Task<string> getDataAreasOwner(Guid ProviderConfigClassID)
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);
            try
            {
                if (!LoadLicensingInformation())
                {
                    LogEntry("Error: License not valid", LogLevels.Error);
                    return "";
                }

                var ProviderEntry = await getDataProvider(ProviderConfigClassID);
                if (ProviderEntry == null)
                {
                    LogEntry($"Could not initialize Provider config class with ID {ProviderConfigClassID}", LogLevels.Warning);
                    return null;
                }

                var lstDataAreas = await ProviderEntry.Provider.getDataAreasAsync(ProviderEntry.Provider.MaxDepth);
                if (lstDataAreas == null || lstDataAreas.Count == 0)
                {
                    LogEntry($"No data areas found for Provider config class with ID {ProviderConfigClassID}", LogLevels.Warning);
                    return null;
                }

                var xDoc = new XmlDocument();
                xDoc.LoadXml("<DataAreaOwners xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"><Owners/></DataAreaOwners>");
                var xNode = xDoc.SelectSingleNode("DataAreaOwners/Owners");

                foreach (var DA in lstDataAreas)
                {
                    var Users = await DA.GetOwnersAsync();
                    if (Users == null)
                        continue;

                    var Persons = getPersonsFromUsers(Users);
                    foreach (var Person in Persons)
                    {
                        var xItem = xDoc.CreateElement("Owner");
                        xNode.AppendChild(xItem);

                        cLIAMHelper.AddXmlElement(xItem, "DataAreaTechnicalName", DA.UID);
                        cLIAMHelper.AddXmlElement(xItem, "PersonID", Person.ToString());
                    }
                }

                using (var sw = new StringWriter())
                {
                    using (var xw = new XmlTextWriter(sw))
                    {
                        xDoc.WriteTo(xw);
                        return sw.ToString();
                    }
                }
            }
            catch (Exception E)
            {
                LogException(E);
                return null;
            }
            finally
            {
                LogMethodEnd(CM);
            }
        }

        [Route("getSecurityGroupsFromProvider"), HttpGet]
        public async Task<IEnumerable<SecurityGroupEntry>> getSecurityGroupsFromProvider(Guid ProviderConfigClassID)
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);
            try
            {
                if (!LoadLicensingInformation())
                {
                    LogEntry("Error: License not valid", LogLevels.Error);
                    return new List<SecurityGroupEntry>();
                }

                var ProviderEntry = await getDataProvider(ProviderConfigClassID);
                if (ProviderEntry == null)
                {
                    LogEntry($"Could not initialize Provider config class with ID {ProviderConfigClassID}", LogLevels.Warning);
                    return new List<SecurityGroupEntry>();
                }

                var lstSecurityGroups = await ProviderEntry.Provider.getSecurityGroupsAsync(ProviderEntry.Provider.GroupFilter);
                if (lstSecurityGroups == null || lstSecurityGroups.Count == 0)
                {
                    LogEntry($"No security groups found for Provider config class with ID {ProviderConfigClassID}", LogLevels.Warning);
                    return new List<SecurityGroupEntry>();
                }

                var SGs = new List<SecurityGroupEntry>();
                foreach (var SecurityGroup in lstSecurityGroups)
                {
                    string uid = null;
                    string scope = null;

                    if (SecurityGroup is cLiamAdGroup adGroup)
                    {
                        uid = adGroup.dn;
                        scope = adGroup.scope;
                    }
                    else if (SecurityGroup is cLiamAdGroup2 adGroup2)
                    {
                        uid = adGroup2.dn;
                        scope = adGroup2.scope;
                    }

                    SGs.Add(new SecurityGroupEntry()
                    {
                        DisplayName = SecurityGroup.TechnicalName,
                        TechnicalName = SecurityGroup.UID,
                        TargetType = ((int)SecurityGroup.Provider.ProviderType).ToString(),
                        UID = uid,
                        Scope = scope
                    });
                }
                return SGs;
            }
            catch (Exception E)
            {
                LogException(E);
                return new List<SecurityGroupEntry>();
            }
            finally
            {
                LogMethodEnd(CM);
            }
        }

        [Route("getDataAreasFromProvider"), HttpGet]
        public async Task<IEnumerable<DataAreaEntry>> getDataAreasFromProvider(Guid ProviderConfigClassID)
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);

            try
            {
                if (!LoadLicensingInformation())
                {
                    LogEntry("Error: License not valid", LogLevels.Error);
                    return Enumerable.Empty<DataAreaEntry>();
                }

                var ProviderEntry = await getDataProvider(ProviderConfigClassID);
                if (ProviderEntry == null)
                {
                    LogEntry($"Could not initialize Provider config class with ID {ProviderConfigClassID}", LogLevels.Warning);
                    return Enumerable.Empty<DataAreaEntry>();
                }

                var lstDataAreas = await ProviderEntry.Provider.getDataAreasAsync(ProviderEntry.Provider.MaxDepth);
                if (lstDataAreas == null || lstDataAreas.Count == 0)
                {
                    LogEntry($"No data areas found for Provider config class with ID {ProviderConfigClassID}", LogLevels.Warning);
                    return Enumerable.Empty<DataAreaEntry>();
                }

                return lstDataAreas.Select(DataArea =>
                {
                    var DataAreaNtfsFolder = DataArea as cLiamNtfsFolder;
                    var DataAreaADGroup = DataArea as cLiamAdGroupAsDataArea;

                    string owner = DataAreaADGroup?.ManagedBySID ?? DataAreaNtfsFolder?.OwnerGroupIdentifier ?? string.Empty;
                    string write = DataAreaADGroup?.UID ?? DataAreaNtfsFolder?.WriteGroupIdentifier ?? string.Empty;
                    return new DataAreaEntry
                    {
                        DisplayName = DataArea.DisplayName ?? string.Empty,
                        UID = DataArea.UID,
                        TechnicalName = DataArea.TechnicalName,
                        Description = DataAreaADGroup?.Description ?? string.Empty,
                        TargetType = ((int)DataArea.Provider.ProviderType).ToString(),
                        ParentUID = DataArea.ParentUID ?? string.Empty,
                        Level = DataArea.Level.ToString(),
                        ConfigurationId = ProviderEntry.ObjectID.ToString(),
                        DataAreaType = DataArea.DataType.ToString(),
                        DataAreaTypeId = (int)DataArea.DataType,
                        Owner = owner,
                        Write = write,
                        Read = DataAreaNtfsFolder?.ReadGroupIdentifier ?? string.Empty,
                        Traverse = DataAreaNtfsFolder?.TraverseGroupIdentifier ?? string.Empty,
                        CreatedDate = DataAreaNtfsFolder?.CreatedDate ?? DateTime.MinValue.ToString("o"),
                    };
                }).ToList();
            }
            catch (Exception E)
            {
                LogException(E);
                return Enumerable.Empty<DataAreaEntry>();
            }
            finally
            {
                LogMethodEnd(CM);
            }
        }

        private async Task<cLiamDataAreaBase> getDataAreaFromUID(string UID)
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);
            try
            {
                var ProviderID = cLIAMHelper.getUidItem(ref UID);
                if (!Guid.TryParse(ProviderID, out var ProviderGuid))
                {
                    LogEntry($"No valid Provider config class ID in UID: {UID}", LogLevels.Warning);
                    return null;
                }

                var ProviderEntry = await getDataProvider(ProviderGuid);
                if (ProviderEntry == null)
                {
                    LogEntry($"Could not initialize Provider config class with ID {ProviderGuid}", LogLevels.Warning);
                    return null;
                }

                var DA = await ProviderEntry.Provider.LoadDataArea(UID);
                if (DA == null)
                {
                    LogEntry($"Could not load data area with UID '{UID}' from provider with ID '{ProviderGuid}'", LogLevels.Warning);
                    return null;
                }

                return DA;
            }
            catch (Exception E)
            {
                LogException(E);
                return null;
            }
            finally
            {
                LogMethodEnd(CM);
            }
        }

        [Route("getChildsFromUID"), HttpGet]
        public async Task<List<LiamDataAreaEntry>> getChildsFromUID(string UID)
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);
            try
            {
                if (!LoadLicensingInformation())
                {
                    LogEntry("Error: License not valid", LogLevels.Error);
                    return new List<LiamDataAreaEntry>();
                }

                var myUID = UID;

                var DA = await getDataAreaFromUID(UID);
                if (DA == null)
                    return new List<LiamDataAreaEntry>();

                var Childs = await DA.getChildrenAsync(0);
                if (Childs == null)
                {
                    LogEntry($"Error while resolving data area children for UID: {UID}", LogLevels.Warning);
                    return new List<LiamDataAreaEntry>();
                }

                var ProviderID = cLIAMHelper.getUidItem(ref myUID);
                var RetVal = Childs.Select(ChildEntry => new LiamDataAreaEntry
                {
                    UID = $"{ProviderID} | {ChildEntry.UID}",
                    DisplayName = ChildEntry.TechnicalName,
                    SupportsPermissions = ChildEntry.SupportsPermissions
                }).ToList();

                return RetVal;
            }
            catch (Exception E)
            {
                LogException(E);
                return new List<LiamDataAreaEntry>();
            }
            finally
            {
                LogMethodEnd(CM);
            }
        }

        [Route("getChildsFromDataArea"), HttpGet]
        public async Task<List<LiamDataAreaEntry>> getChildsFromDataArea(Guid DataAreaClassID)
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);
            try
            {
                if (!LoadLicensingInformation())
                {
                    LogEntry("Error: License not valid", LogLevels.Error);
                    return new List<LiamDataAreaEntry>();
                }

                var DA = await getDataAreaFromId(DataAreaClassID);
                if (DA == null)
                    return new List<LiamDataAreaEntry>();

                var lstChilds = await DA.dataArea.getChildrenAsync();
                if (lstChilds == null)
                {
                    LogEntry($"Error getting children of data area with UID: {DA.dataArea.UID}", LogLevels.Warning);
                    return new List<LiamDataAreaEntry>();
                }

                LogEntry($"Number of children: {lstChilds.Count}", LogLevels.Debug);

                var RetVal = lstChilds.Select(ChildEntry => new LiamDataAreaEntry
                {
                    UID = $"{DA.configId} | {ChildEntry.UID}",
                    DisplayName = ChildEntry.TechnicalName,
                    SupportsPermissions = ChildEntry.SupportsPermissions
                }).ToList();

                return RetVal;
            }
            catch (Exception E)
            {
                LogException(E);
                return new List<LiamDataAreaEntry>();
            }
            finally
            {
                LogMethodEnd(CM);
            }
        }

        internal class cGetDataAreaResult
        {
            internal cLiamDataAreaBase dataArea;
            internal Guid configId;
        }

        private async Task<cGetDataAreaResult> getDataAreaFromId(Guid DataAreaClassID)
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);
            try
            {
                var ClassID = SPSDataEngineSchemaReader.ClassGetIDFromName(constFragmentNameDataAreaMain);
                LogEntry($"Data area class ID: {ClassID}", LogLevels.Debug);
                var FragDataArea = FragmentRequest.GetSPSFragment(ClassID, DataAreaClassID, "DataAreaConfiguration,technicalName, Owner.uniqueID as ownerRef");
                if (FragDataArea == null)
                {
                    LogEntry($"Data area fragment not found: ClassID={ClassID}, FragmentId={DataAreaClassID}", LogLevels.Debug);
                    return null;
                }

                var ConfigID = cLIAMHelper.getGuidFromObject(FragDataArea["DataAreaConfiguration"]);
                if (ConfigID == Guid.Empty)
                {
                    LogEntry("DataAreaConfiguration value not found.", LogLevels.Warning);
                    return null;
                }
                LogEntry($"Config provider class ID: {ConfigID}", LogLevels.Debug);

                var ConfigProviderEntry = await getDataProvider(ConfigID);
                if (ConfigProviderEntry == null)
                {
                    LogEntry($"Could not load Config Provider from cache with ID: {ConfigID}", LogLevels.Debug);
                    return null;
                }

                var UID = cLIAMHelper.getStringFromObject(FragDataArea["technicalName"]);
                LogEntry($"Data area UID: {UID}", LogLevels.Debug);

                var DA = await ConfigProviderEntry.Provider.LoadDataArea(UID);
                DA.OwnerRef = cLIAMHelper.getStringFromObject(FragDataArea["ownerRef"]);
                if (DA == null)
                {
                    LogEntry($"Could not find LIAM data area with UID: {UID}", LogLevels.Debug);
                    return null;
                }

                return new cGetDataAreaResult
                {
                    dataArea = DA,
                    configId = ConfigID
                };
            }
            catch (Exception E)
            {
                LogException(E);
                return null;
            }
            finally
            {
                LogMethodEnd(CM);
            }
        }

        [Route("getOwnerInfosFromDataArea"), HttpGet]
        public async Task<List<cLiamUserInfo>> getOwnerInfosFromDataArea(Guid DataAreaClassID)
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);
            try
            {
                if (!LoadLicensingInformation())
                {
                    LogEntry("Error: License not valid", LogLevels.Error);
                    return new List<cLiamUserInfo>();
                }

                var DAResult = await getDataAreaFromId(DataAreaClassID);
                if (DAResult == null)
                    return new List<cLiamUserInfo>();

                var lstOwners = await DAResult.dataArea.GetOwnersAsync();
                if (lstOwners == null)
                {
                    LogEntry($"Error getting owners of data area with UID: {DAResult.dataArea.UID}", LogLevels.Warning);
                    return new List<cLiamUserInfo>();
                }
                LogEntry($"Number of owners: {lstOwners.Count}", LogLevels.Debug);

                return lstOwners;
            }
            catch (Exception E)
            {
                LogException(E);
                return new List<cLiamUserInfo>();
            }
            finally
            {
                LogMethodEnd(CM);
            }
        }

        private List<Guid> getPersonsFromUsers(List<cLiamUserInfo> Users)
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);
            try
            {
                var AccountAdClassId = SPSDataEngineSchemaReader.ClassGetIDFromName(constFragmentNameAccountAd);
                var AccountBaseClassId = SPSDataEngineSchemaReader.ClassGetIDFromName(constFragmentNameAccountBase);
                var UserBaseClassId = SPSDataEngineSchemaReader.ClassGetIDFromName(constFragmentNameUserBase);
                var CommonClassId = SPSDataEngineSchemaReader.ClassGetIDFromName(constFragmentNameCommon);

                var ListOwners = new List<Guid>();
                foreach (var OwnerEntry in Users)
                {
                    if (string.IsNullOrEmpty(OwnerEntry.UserPrincipalName))
                        continue;

                    var FilterValues = new List<string> { OwnerEntry.UserPrincipalName };
                    var Filter = AsqlHelper.BuildInCondition("UserPrincipalName", FilterValues);
                    LogEntry($"ASql Filter: {Filter}");

                    var tbl = FragmentRequestBase.SimpleLoad(AccountAdClassId, "[Expression-ObjectID] as Id, UserPrincipalName", Filter);
                    if (tbl?.Rows == null)
                    {
                        LogEntry($"No AD account entry list found with UserPrincipalName='{OwnerEntry.UserPrincipalName}'", LogLevels.Warning);
                        continue;
                    }

                    foreach (DataRow AdEntry in tbl.Rows)
                    {
                        var AdId = cLIAMHelper.getGuidFromObject(AdEntry["ID"]);
                        if (AdId == Guid.Empty)
                        {
                            LogEntry("No expression object ID found for AccountAd entry", LogLevels.Warning);
                            continue;
                        }

                        Guid[] ids2 = { AdId };
                        var tbl2 = FragmentRequestBase.SimpleLoad(AccountBaseClassId, "Owner", AsqlHelper.BuildInCondition("[Expression-ObjectID]", ids2));
                        if (tbl2?.Rows == null || tbl2.Rows.Count == 0)
                        {
                            LogEntry($"Account class base fragment not found: ClassId={AccountBaseClassId}, ObjectId={AdId}", LogLevels.Debug);
                            continue;
                        }

                        var OwnerID = cLIAMHelper.getGuidFromObject(tbl2.Rows[0]["Owner"]);
                        if (OwnerID == Guid.Empty)
                        {
                            LogEntry($"Could not get Owner ID for account class base: ObjectId={AdId}", LogLevels.Debug);
                            continue;
                        }

                        var FragUserBase = FragmentRequest.GetSPSFragment(UserBaseClassId, OwnerID);
                        if (FragUserBase == null)
                        {
                            LogEntry($"User class base entry not found: ClassID={UserBaseClassId}, FragmentId={OwnerID}", LogLevels.Debug);
                            continue;
                        }
                        var UserID = cLIAMHelper.getGuidFromObject(FragUserBase["ID"]);
                        var UserObjectID = cLIAMHelper.getGuidFromObject(FragUserBase["Expression-ObjectID"]);

                        Guid[] ids3 = { UserObjectID };
                        var tbl3 = FragmentRequestBase.SimpleLoad(CommonClassId, "State", AsqlHelper.BuildInCondition("[Expression-ObjectID]", ids3));
                        if (tbl3?.Rows == null || tbl3.Rows.Count == 0)
                        {
                            LogEntry($"Common class base fragment not found: ClassId={CommonClassId}, ObjectId={UserObjectID}", LogLevels.Debug);
                            continue;
                        }
                        var State = cLIAMHelper.getIntFromObject(tbl3.Rows[0]["State"]);
                        LogEntry($"User found: ID={UserID}, ObjectID={UserObjectID}, State={State}", LogLevels.Debug);
                        if (State == 2023)
                            ListOwners.Add(UserID);
                        else
                            LogEntry("User is not active", LogLevels.Debug);
                    }
                }
                return ListOwners;
            }
            catch (Exception E)
            {
                LogException(E);
                return new List<Guid>();
            }
            finally
            {
                LogMethodEnd(CM);
            }
        }

        [Route("getOwnersFromDataArea"), HttpGet]
        public async Task<List<Guid>> getOwnersFromDataArea(Guid DataAreaClassID)
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);
            try
            {
                var OwnersInfo = await getOwnerInfosFromDataArea(DataAreaClassID);
                if (OwnersInfo == null)
                    return new List<Guid>();

                return getPersonsFromUsers(OwnersInfo);
            }
            catch (Exception E)
            {
                LogException(E);
                return new List<Guid>();
            }
            finally
            {
                LogMethodEnd(CM);
            }
        }
#if DEBUG
        [Route("grantPermissionByEMail_Debug"), HttpGet]
        public async Task<cLiamPermissionResult> grantPermissionByEMail_Debug(string UID, string EMail, int AccessType)
        {
            return await grantPermissionByEMail(UID, EMail, AccessType);
        }
#endif //DEBUG

        [Route("grantPermission"), HttpPost]
        public async Task<cLiamPermissionResult> grantPermission(Guid DataAreaId, Guid PersonId, int AccessType, Guid AccoundId)
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);
            try
            {
                if (!LoadLicensingInformation())
                {
                    LogEntry("Error: License not valid", LogLevels.Error);
                    return new cLiamPermissionResult { Valid = false };
                }

                var DA = await getDataAreaFromId(DataAreaId);
                if (DA == null)
                    return null;

                var UPN = GetUserPrincipalName(PersonId, AccoundId);
                if (string.IsNullOrEmpty(UPN))
                    return null;

                var User = new cLiamUserInfo
                {
                    EMail = UPN,
                    UserPrincipalName = UPN
                };

                return await DA.dataArea.GrantPermissionAsync(User, (eLiamAccessRoles)AccessType);
            }
            catch (Exception E)
            {
                LogException(E);
                return null;
            }
            finally
            {
                LogMethodEnd(CM);
            }
        }

        [Route("grantPermission"), HttpPost]
        public async Task<cLiamPermissionResult> grantPermission(Guid DataAreaId, Guid PersonId, int AccessType)
        {
            return await grantPermission(DataAreaId, PersonId, AccessType, Guid.Empty);
        }

        public class RevokePermissionResponse
        {
            public bool success { get; set; }
            public string error { get; set; } // null wenn success=true
        }

        [Route("revokePermission"), HttpPost]
        public async Task<RevokePermissionResponse> revokePermission(Guid DataAreaId, Guid PersonId, int AccessType, Guid AccoundId)
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);
            try
            {
                if (!LoadLicensingInformation())
                {
                    LogEntry("Error: License not valid", LogLevels.Error);
                    return new RevokePermissionResponse { success = false, error = "License not valid" };
                }

                var DA = await getDataAreaFromId(DataAreaId);
                if (DA == null)
                    return new RevokePermissionResponse { success = false, error = "DataArea not found" };

                var UPN = GetUserPrincipalName(PersonId, AccoundId);
                if (string.IsNullOrEmpty(UPN))
                    return new RevokePermissionResponse { success = false, error = "UPN not found" };

                // Optional: Enum-Validierung, verhindert interne Fehler
                if (!Enum.IsDefined(typeof(eLiamAccessRoles), AccessType))
                    return new RevokePermissionResponse { success = false, error = "Invalid AccessType" };

                var user = new cLiamUserInfo { EMail = UPN, UserPrincipalName = UPN };

                var ok = await DA.dataArea.RevokePermissionAsync(user, (eLiamAccessRoles)AccessType);
                return new RevokePermissionResponse { success = ok, error = ok ? null : "RevokePermissionAsync failed" };
            }
            catch (Exception ex)
            {
                LogException(ex);
                return new RevokePermissionResponse { success = false, error = ex.Message };
            }
            finally
            {
                LogMethodEnd(CM);
            }
        }
        [Route("revokePermission"), HttpPost]
        public async Task<RevokePermissionResponse> revokePermission(Guid DataAreaId, Guid PersonId, int AccessType)
        {
            return await revokePermission(DataAreaId, PersonId, AccessType, Guid.Empty);
        }

        [Route("grantPermissionByEMail"), HttpPost]
        public async Task<cLiamPermissionResult> grantPermissionByEMail(string UID, string EMail, int AccessType)
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);
            try
            {
                if (!LoadLicensingInformation())
                {
                    LogEntry("Error: License not valid", LogLevels.Error);
                    return new cLiamPermissionResult { Valid = false };
                }

                var DA = await getDataAreaFromUID(UID);
                if (DA == null)
                    return null;

                var User = new cLiamUserInfo { EMail = EMail };

                return await DA.GrantPermissionAsync(User, (eLiamAccessRoles)AccessType);
            }
            catch (Exception E)
            {
                LogException(E);
                return null;
            }
            finally
            {
                LogMethodEnd(CM);
            }
        }

        [Route("revokePermissionByEMail"), HttpPost]
        public async Task<bool> revokePermissionByEMail(string UID, string EMail, int AccessType)
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);
            try
            {
                if (!LoadLicensingInformation())
                {
                    LogEntry("Error: License not valid", LogLevels.Error);
                    return false;
                }

                var DA = await getDataAreaFromUID(UID);
                if (DA == null)
                    return false;

                var User = new cLiamUserInfo { EMail = EMail };

                return await DA.RevokePermissionAsync(User, (eLiamAccessRoles)AccessType);
            }
            catch (Exception E)
            {
                LogException(E);
                return false;
            }
            finally
            {
                LogMethodEnd(CM);
            }
        }

        [Route("encryptPassword"), HttpGet]
        public string encryptPassword(string Password)
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);
            try
            {
                return CryptoManager.Instance.EncryptDBText(Password);
            }
            catch (Exception E)
            {
                LogException(E);
                return null;
            }
            finally
            {
                LogMethodEnd(CM);
            }
        }

        [Route("getLicenseInformation"), HttpGet]
        public cC4ITLicenseM42ESM getLicenseInformation()
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);
            try
            {
                if (!LoadLicensingInformation())
                    return null;
                return cC4ITLicenseM42ESM.Instance;
            }
            catch (Exception E)
            {
                LogException(E);
                return null;
            }
            finally
            {
                LogMethodEnd(CM);
            }
        }

        [Route("getLicensedModules"), HttpGet]
        public List<dynamic> getLicensedModules()
        {
            var CM = MethodBase.GetCurrentMethod();
            LogMethodBegin(CM);
            try
            {
                if (!LoadLicensingInformation())
                    return new List<dynamic>();

                var modules = cC4ITLicenseM42ESM.Instance?.Modules;
                if (modules == null || !cC4ITLicenseM42ESM.Instance.IsValid)
                    return new List<dynamic>();

                var rs = modules.Keys.Select(k => k.ToString()).ToList();

                var ClassID = SPSDataEngineSchemaReader.ClassGetIDFromName(constFragmentNameDataAreaTargetPickupType);
                LogEntry($"Addon Config class ID: {ClassID}", LogLevels.Debug);

                var dataTable = FragmentRequest.SimpleLoad(ClassID, "Id, Value", AsqlHelper.BuildInCondition("ID", rs));
                if (dataTable?.Rows == null || dataTable.Rows.Count == 0)
                {
                    LogEntry($"Addon Config fragment not found: ClassID={ClassID}", LogLevels.Debug);
                    return null;
                }

                var rs2 = new List<dynamic>();
                foreach (DataRow p in dataTable.Rows)
                {
                    var moduleId = cLIAMHelper.getGuidFromObject(p["Id"]);
                    var module = cC4ITLicenseM42ESM.Instance.Modules[moduleId];
                    var obj = cLiamM42Helper.ToDynamic(module);
                    obj.Value = cLIAMHelper.getStringFromObject(p["Value"]);
                    rs2.Add(obj);
                }
                return rs2;
            }
            catch (Exception E)
            {
                LogException(E);
                return null;
            }
            finally
            {
                LogMethodEnd(CM);
            }
        }

        private string GetUserPrincipalName(Guid PersonId, Guid AccoundId)
        {
            Guid myAccountId = AccoundId != Guid.Empty ? AccoundId : GetPrimaryAccountId(PersonId);
            if (myAccountId == Guid.Empty)
                return null;

            var accountEoid = GetAccountEOID(myAccountId);
            if (accountEoid == Guid.Empty)
                return null;

            return GetUserPrincipalNameFromEOID(accountEoid);
        }

        private Guid GetPrimaryAccountId(Guid PersonId)
        {
            var ClassID = SPSDataEngineSchemaReader.ClassGetIDFromName(constFragmentNameUserBase);
            LogEntry($"Data area class ID: {ClassID}", LogLevels.Debug);
            var FragDataArea = FragmentRequest.GetSPSFragment(ClassID, PersonId);
            if (FragDataArea == null)
            {
                LogEntry($"Data area fragment not found: ClassID={ClassID}, FragmentId={PersonId}", LogLevels.Debug);
                return Guid.Empty;
            }

            var myAccountId = cLIAMHelper.getGuidFromObject(FragDataArea["PrimaryAccount"]);
            if (myAccountId == Guid.Empty)
                LogEntry("Account value not found.", LogLevels.Warning);
            else
                LogEntry($"Account class ID: {myAccountId}", LogLevels.Debug);

            return myAccountId;
        }

        private Guid GetAccountEOID(Guid myAccountId)
        {
            var ClassID = SPSDataEngineSchemaReader.ClassGetIDFromName(constFragmentNameAccountBase);
            LogEntry($"Data area class ID: {ClassID}", LogLevels.Debug);
            var FragDataArea = FragmentRequest.GetSPSFragment(ClassID, myAccountId);
            if (FragDataArea == null)
            {
                LogEntry($"Data area fragment not found: ClassID={ClassID}, FragmentId={myAccountId}", LogLevels.Debug);
                return Guid.Empty;
            }

            var accountEoid = cLIAMHelper.getGuidFromObject(FragDataArea["Expression-ObjectId"]);
            if (accountEoid == Guid.Empty)
                LogEntry("Account EOID value not found.", LogLevels.Warning);
            else
                LogEntry($"Account EOID: {accountEoid}", LogLevels.Debug);

            return accountEoid;
        }

        private string GetUserPrincipalNameFromEOID(Guid accountEoid)
        {
            var ClassID = SPSDataEngineSchemaReader.ClassGetIDFromName(constFragmentNameAccountAd);
            LogEntry($"Data area class ID: {ClassID}", LogLevels.Debug);
            var tbl = FragmentRequestBase.SimpleLoad(ClassID, "UserPrincipalName", $"[Expression-ObjectId]='{accountEoid}'");
            if (tbl?.Rows == null || tbl.Rows.Count == 0)
            {
                LogEntry($"No AD account entry list found with eoid='{accountEoid}'", LogLevels.Warning);
                return null;
            }

            var UPN = cLIAMHelper.getStringFromObject(tbl.Rows[0]["UserPrincipalName"]);
            if (string.IsNullOrEmpty(UPN))
            {
                LogEntry("No UserPrincipalName found for AccountAd entry", LogLevels.Warning);
                return null;
            }
            return UPN;
        }

        public class ProviderConfigurationExport
        {
            public Guid ProviderConfigClassID { get; set; }
            public Guid ProviderConfigObjectID { get; set; }
            public string SanitizedJson { get; set; }
            public cLiamProviderData Configuration { get; set; }
            public DateTime GeneratedAtUtc { get; set; }
        }
    }
}