using System;
using System.Collections.Generic;
using System.Reflection;
using Serilog;
using SyncEngine.Configuration;

namespace SyncEngine.Access
{
    /// <summary>COM helpers for CurrentDb QueryDefs/Relations via Access.Application.</summary>
    internal static class AccessDaoHelper
    {
        internal static void ReadRelations(
            object database,
            IList<ForeignKeyInfo> keys,
            HashSet<string> tableSet,
            ILogger logger)
        {
            var relations = database.GetType().InvokeMember(
                "Relations",
                BindingFlags.GetProperty,
                null,
                database,
                null);

            var count = Convert.ToInt32(relations.GetType().InvokeMember(
                "Count",
                BindingFlags.GetProperty,
                null,
                relations,
                null));

            for (var i = 0; i < count; i++)
            {
                object relation = null;
                try
                {
                    relation = relations.GetType().InvokeMember(
                        "Item",
                        BindingFlags.GetProperty,
                        null,
                        relations,
                        new object[] { i });

                    var childTable = Convert.ToString(relation.GetType().InvokeMember(
                        "ForeignTable",
                        BindingFlags.GetProperty,
                        null,
                        relation,
                        null));

                    var parentTable = Convert.ToString(relation.GetType().InvokeMember(
                        "Table",
                        BindingFlags.GetProperty,
                        null,
                        relation,
                        null));

                    if (!tableSet.Contains(childTable) || !tableSet.Contains(parentTable))
                    {
                        continue;
                    }

                    var attributes = Convert.ToInt32(relation.GetType().InvokeMember(
                        "Attributes",
                        BindingFlags.GetProperty,
                        null,
                        relation,
                        null));
                    AccessSchemaReader.ParseRelationGrbit(attributes, out var onUpdateCascade, out var onDeleteCascade);

                    var fields = relation.GetType().InvokeMember(
                        "Fields",
                        BindingFlags.GetProperty,
                        null,
                        relation,
                        null);

                    var fieldCount = Convert.ToInt32(fields.GetType().InvokeMember(
                        "Count",
                        BindingFlags.GetProperty,
                        null,
                        fields,
                        null));

                    for (var f = 0; f < fieldCount; f++)
                    {
                        object field = null;
                        try
                        {
                            field = fields.GetType().InvokeMember(
                                "Item",
                                BindingFlags.GetProperty,
                                null,
                                fields,
                                new object[] { f });

                            var childColumn = Convert.ToString(field.GetType().InvokeMember(
                                "Name",
                                BindingFlags.GetProperty,
                                null,
                                field,
                                null));

                            var parentColumn = Convert.ToString(field.GetType().InvokeMember(
                                "ForeignName",
                                BindingFlags.GetProperty,
                                null,
                                field,
                                null));

                            keys.Add(new ForeignKeyInfo
                            {
                                ChildTable = childTable,
                                ChildColumn = childColumn,
                                ParentTable = parentTable,
                                ParentColumn = parentColumn,
                                ConstraintName = "FK_" + childTable + "_" + parentTable,
                                OnUpdateCascade = onUpdateCascade,
                                OnDeleteCascade = onDeleteCascade
                            });
                        }
                        catch (Exception ex)
                        {
                            if (logger != null)
                            {
                                logger.Debug("Skipped relation field {Index} ({Message})", f, FormatException(ex));
                            }
                        }
                        finally
                        {
                            ReleaseCom(field);
                        }
                    }

                    ReleaseCom(fields);
                }
                catch (Exception ex)
                {
                    if (logger != null)
                    {
                        logger.Debug("Skipped relation index {Index} ({Message})", i, FormatException(ex));
                    }
                }
                finally
                {
                    ReleaseCom(relation);
                }
            }

            ReleaseCom(relations);
        }

        internal static void ReadFieldMetadata(
            object database,
            IList<AccessTableSchema> tables,
            HashSet<string> tableSet,
            ILogger logger)
        {
            var tableMap = new Dictionary<string, AccessTableSchema>(StringComparer.OrdinalIgnoreCase);
            foreach (var table in tables)
            {
                tableMap[table.TableName] = table;
            }

            object tableDefs = null;
            try
            {
                tableDefs = database.GetType().InvokeMember(
                    "TableDefs",
                    BindingFlags.GetProperty,
                    null,
                    database,
                    null);

                var count = Convert.ToInt32(tableDefs.GetType().InvokeMember(
                    "Count",
                    BindingFlags.GetProperty,
                    null,
                    tableDefs,
                    null));

                for (var i = 0; i < count; i++)
                {
                    object tableDef = null;
                    try
                    {
                        tableDef = tableDefs.GetType().InvokeMember(
                            "Item",
                            BindingFlags.GetProperty,
                            null,
                            tableDefs,
                            new object[] { i });

                        var tableName = Convert.ToString(tableDef.GetType().InvokeMember(
                            "Name",
                            BindingFlags.GetProperty,
                            null,
                            tableDef,
                            null));

                        if (!tableSet.Contains(tableName) || !tableMap.TryGetValue(tableName, out var schema))
                        {
                            continue;
                        }

                        try
                        {
                            var tableRule = Convert.ToString(tableDef.GetType().InvokeMember(
                                "ValidationRule",
                                BindingFlags.GetProperty,
                                null,
                                tableDef,
                                null));
                            if (!string.IsNullOrWhiteSpace(tableRule))
                            {
                                schema.TableValidationRule = tableRule.Trim();
                            }
                        }
                        catch (Exception ex)
                        {
                            logger?.Debug("Skipped TableDef.ValidationRule on {Table} ({Message})", tableName, FormatException(ex));
                        }

                        ReadFieldProperties(tableDef, schema, logger);
                    }
                    catch (Exception ex)
                    {
                        logger?.Debug("Skipped TableDef metadata {Index} ({Message})", i, FormatException(ex));
                    }
                    finally
                    {
                        ReleaseCom(tableDef);
                    }
                }
            }
            finally
            {
                ReleaseCom(tableDefs);
            }
        }

        private static void ReadFieldProperties(object tableDef, AccessTableSchema schema, ILogger logger)
        {
            object fields = null;
            try
            {
                fields = tableDef.GetType().InvokeMember(
                    "Fields",
                    BindingFlags.GetProperty,
                    null,
                    tableDef,
                    null);

                var count = Convert.ToInt32(fields.GetType().InvokeMember(
                    "Count",
                    BindingFlags.GetProperty,
                    null,
                    fields,
                    null));

                var columnMap = new Dictionary<string, AccessColumnSchema>(StringComparer.OrdinalIgnoreCase);
                foreach (var col in schema.Columns)
                {
                    columnMap[col.Name] = col;
                }

                for (var f = 0; f < count; f++)
                {
                    object field = null;
                    try
                    {
                        field = fields.GetType().InvokeMember(
                            "Item",
                            BindingFlags.GetProperty,
                            null,
                            fields,
                            new object[] { f });

                        var fieldName = Convert.ToString(field.GetType().InvokeMember(
                            "Name",
                            BindingFlags.GetProperty,
                            null,
                            field,
                            null));

                        if (!columnMap.TryGetValue(fieldName, out var column))
                        {
                            continue;
                        }

                        var defaultValue = Convert.ToString(field.GetType().InvokeMember(
                            "DefaultValue",
                            BindingFlags.GetProperty,
                            null,
                            field,
                            null));
                        if (!string.IsNullOrWhiteSpace(defaultValue))
                        {
                            column.DefaultValue = defaultValue.Trim();
                        }

                        var validationRule = Convert.ToString(field.GetType().InvokeMember(
                            "ValidationRule",
                            BindingFlags.GetProperty,
                            null,
                            field,
                            null));
                        if (!string.IsNullOrWhiteSpace(validationRule))
                        {
                            column.ValidationRule = validationRule.Trim();
                        }
                    }
                    catch (Exception ex)
                    {
                        logger?.Debug("Skipped field metadata {Index} ({Message})", f, FormatException(ex));
                    }
                    finally
                    {
                        ReleaseCom(field);
                    }
                }
            }
            finally
            {
                ReleaseCom(fields);
            }
        }

        internal static void ReadTableIndexes(
            object database,
            IList<AccessIndexInfo> indexes,
            HashSet<string> tableSet,
            ILogger logger)
        {
            object tableDefs = null;
            try
            {
                tableDefs = database.GetType().InvokeMember(
                    "TableDefs",
                    BindingFlags.GetProperty,
                    null,
                    database,
                    null);

                var count = Convert.ToInt32(tableDefs.GetType().InvokeMember(
                    "Count",
                    BindingFlags.GetProperty,
                    null,
                    tableDefs,
                    null));

                for (var i = 0; i < count; i++)
                {
                    object tableDef = null;
                    try
                    {
                        tableDef = tableDefs.GetType().InvokeMember(
                            "Item",
                            BindingFlags.GetProperty,
                            null,
                            tableDefs,
                            new object[] { i });

                        var tableName = Convert.ToString(tableDef.GetType().InvokeMember(
                            "Name",
                            BindingFlags.GetProperty,
                            null,
                            tableDef,
                            null));

                        if (!tableSet.Contains(tableName))
                        {
                            continue;
                        }

                        ReadIndexesForTable(tableDef, tableName, indexes, logger);
                    }
                    catch (Exception ex)
                    {
                        if (logger != null)
                        {
                            logger.Debug("Skipped TableDef index {Index} ({Message})", i, FormatException(ex));
                        }
                    }
                    finally
                    {
                        ReleaseCom(tableDef);
                    }
                }
            }
            finally
            {
                ReleaseCom(tableDefs);
            }
        }

        private static void ReadIndexesForTable(
            object tableDef,
            string tableName,
            IList<AccessIndexInfo> indexes,
            ILogger logger)
        {
            object indexCollection = null;
            try
            {
                indexCollection = tableDef.GetType().InvokeMember(
                    "Indexes",
                    BindingFlags.GetProperty,
                    null,
                    tableDef,
                    null);

                var count = Convert.ToInt32(indexCollection.GetType().InvokeMember(
                    "Count",
                    BindingFlags.GetProperty,
                    null,
                    indexCollection,
                    null));

                for (var i = 0; i < count; i++)
                {
                    object index = null;
                    try
                    {
                        index = indexCollection.GetType().InvokeMember(
                            "Item",
                            BindingFlags.GetProperty,
                            null,
                            indexCollection,
                            new object[] { i });

                        var indexName = Convert.ToString(index.GetType().InvokeMember(
                            "Name",
                            BindingFlags.GetProperty,
                            null,
                            index,
                            null));

                        if (string.IsNullOrEmpty(indexName) || indexName.StartsWith("~", StringComparison.Ordinal))
                        {
                            continue;
                        }

                        var isPrimary = Convert.ToBoolean(index.GetType().InvokeMember(
                            "Primary",
                            BindingFlags.GetProperty,
                            null,
                            index,
                            null));

                        var isUnique = Convert.ToBoolean(index.GetType().InvokeMember(
                            "Unique",
                            BindingFlags.GetProperty,
                            null,
                            index,
                            null));

                        var columns = ReadIndexColumns(index, logger);
                        if (columns.Count == 0)
                        {
                            continue;
                        }

                        indexes.Add(new AccessIndexInfo
                        {
                            TableName = tableName,
                            IndexName = indexName,
                            IsPrimary = isPrimary,
                            IsUnique = isUnique || isPrimary,
                            Columns = columns
                        });
                    }
                    catch (Exception ex)
                    {
                        if (logger != null)
                        {
                            logger.Debug(
                                "Skipped index {Index} on {Table} ({Message})",
                                i,
                                tableName,
                                FormatException(ex));
                        }
                    }
                    finally
                    {
                        ReleaseCom(index);
                    }
                }
            }
            finally
            {
                ReleaseCom(indexCollection);
            }
        }

        private static IList<string> ReadIndexColumns(object index, ILogger logger)
        {
            var columns = new List<string>();
            object fields = null;
            try
            {
                fields = index.GetType().InvokeMember(
                    "Fields",
                    BindingFlags.GetProperty,
                    null,
                    index,
                    null);

                var count = Convert.ToInt32(fields.GetType().InvokeMember(
                    "Count",
                    BindingFlags.GetProperty,
                    null,
                    fields,
                    null));

                for (var f = 0; f < count; f++)
                {
                    object field = null;
                    try
                    {
                        field = fields.GetType().InvokeMember(
                            "Item",
                            BindingFlags.GetProperty,
                            null,
                            fields,
                            new object[] { f });

                        var columnName = Convert.ToString(field.GetType().InvokeMember(
                            "Name",
                            BindingFlags.GetProperty,
                            null,
                            field,
                            null));

                        if (!string.IsNullOrWhiteSpace(columnName))
                        {
                            columns.Add(columnName);
                        }
                    }
                    catch (Exception ex)
                    {
                        if (logger != null)
                        {
                            logger.Debug("Skipped index field {Index} ({Message})", f, FormatException(ex));
                        }
                    }
                    finally
                    {
                        ReleaseCom(field);
                    }
                }
            }
            finally
            {
                ReleaseCom(fields);
            }

            return columns;
        }

        internal static void ReadQueryDefs(object database, IList<AccessQuerySchema> queries, ILogger logger)
        {
            const int dbQSelect = 0;
            var queryDefs = database.GetType().InvokeMember(
                "QueryDefs",
                BindingFlags.GetProperty,
                null,
                database,
                null);

            var count = Convert.ToInt32(queryDefs.GetType().InvokeMember(
                "Count",
                BindingFlags.GetProperty,
                null,
                queryDefs,
                null));

            for (var i = 0; i < count; i++)
            {
                object queryDef = null;
                try
                {
                    queryDef = queryDefs.GetType().InvokeMember(
                        "Item",
                        BindingFlags.GetProperty,
                        null,
                        queryDefs,
                        new object[] { i });

                    var name = Convert.ToString(queryDef.GetType().InvokeMember(
                        "Name",
                        BindingFlags.GetProperty,
                        null,
                        queryDef,
                        null));

                    if (string.IsNullOrEmpty(name) || name.StartsWith("~", StringComparison.Ordinal))
                    {
                        continue;
                    }

                    var queryType = Convert.ToInt32(queryDef.GetType().InvokeMember(
                        "Type",
                        BindingFlags.GetProperty,
                        null,
                        queryDef,
                        null));

                    if (queryType != dbQSelect)
                    {
                        continue;
                    }

                    var sqlText = Convert.ToString(queryDef.GetType().InvokeMember(
                        "SQL",
                        BindingFlags.GetProperty,
                        null,
                        queryDef,
                        null));

                    var query = new AccessQuerySchema { Name = name, SqlText = sqlText ?? "" };
                    AccessSchemaReader.ClassifyQuery(query);
                    queries.Add(query);
                }
                catch (Exception ex)
                {
                    if (logger != null)
                    {
                        logger.Debug("Skipped QueryDef index {Index} ({Message})", i, FormatException(ex));
                    }
                }
                finally
                {
                    ReleaseCom(queryDef);
                }
            }

            ReleaseCom(queryDefs);
        }

        internal static void ReadQueryDefs(object database, IList<AccessQuerySchema> queries)
        {
            ReadQueryDefs(database, queries, null);
        }

        internal static string FormatException(Exception ex)
        {
            var current = ex;
            while (current is TargetInvocationException && current.InnerException != null)
            {
                current = current.InnerException;
            }

            return current.Message;
        }

        internal static void ReleaseCom(object comObject)
        {
            if (comObject == null)
            {
                return;
            }

            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(comObject);
            }
            catch
            {
                // ignore release errors during cleanup
            }
        }
    }
}