using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using Serilog;
using SyncEngine.Configuration;

namespace SyncEngine.Access
{
    public sealed class AccessSchemaReader
    {
        private readonly AccessConnectionFactory _factory;
        private readonly SyncOptions _options;
        private readonly ILogger _logger;

        public AccessSchemaReader(AccessConnectionFactory factory, SyncOptions options, ILogger logger)
        {
            _factory = factory;
            _options = options;
            _logger = logger;
        }

        public IList<AccessTableSchema> ReadTables()
        {
            using (var conn = _factory.Create())
            {
                conn.Open();
                var tableNames = conn.GetSchema("Tables")
                    .AsEnumerable()
                    .Where(r => string.Equals(Convert.ToString(r["TABLE_TYPE"]), "TABLE", StringComparison.OrdinalIgnoreCase))
                    .Select(r => Convert.ToString(r["TABLE_NAME"]))
                    .Where(n => !TableExcludeFilter.IsSystemTable(n))
                    .OrderBy(n => n, StringComparer.OrdinalIgnoreCase)
                    .ToList();

                var included = TableExcludeFilter.FilterTables(tableNames, _options.AccessSqlExcludeTables);
                _logger.Information("Access tables: {Total} discovered, {Excluded} excluded, {Included} in scope",
                    tableNames.Count,
                    tableNames.Count - included.Count,
                    included.Count);

                var schemas = new List<AccessTableSchema>();
                foreach (var table in included)
                {
                    schemas.Add(ReadTableSchema(conn, table));
                }

                return schemas;
            }
        }

        /// <summary>All user tables (ignores ACCESS_SQL_EXCLUDE_TABLES) for expanding table.* in views.</summary>
        public ViewColumnCatalog ReadViewColumnCatalog()
        {
            var map = new Dictionary<string, IReadOnlyList<string>>(StringComparer.OrdinalIgnoreCase);
            using (var conn = _factory.Create())
            {
                conn.Open();
                var tableNames = conn.GetSchema("Tables")
                    .AsEnumerable()
                    .Where(r => string.Equals(Convert.ToString(r["TABLE_TYPE"]), "TABLE", StringComparison.OrdinalIgnoreCase))
                    .Select(r => Convert.ToString(r["TABLE_NAME"]))
                    .Where(n => !TableExcludeFilter.IsSystemTable(n))
                    .OrderBy(n => n, StringComparer.OrdinalIgnoreCase)
                    .ToList();

                foreach (var table in tableNames)
                {
                    var schema = ReadTableSchema(conn, table);
                    map[table] = schema.Columns.Select(c => c.Name).ToList();
                }
            }

            _logger.Debug("View column catalog loaded for {Count} Access tables", map.Count);
            return new ViewColumnCatalog(map);
        }

        public AccessSysMetadata ReadSysMetadata(IList<AccessTableSchema> tables)
        {
            var tableSet = new HashSet<string>(tables.Select(t => t.TableName), StringComparer.OrdinalIgnoreCase);
            var metadata = new AccessSysMetadata();

            var queriesOk = TryReadQueriesFromMsysStandalone(metadata.Queries);
            var foreignKeysOk = TryReadForeignKeysStandalone(metadata.ForeignKeys, tableSet);
            var indexesOk = TryReadIndexesStandalone(metadata.Indexes, tableSet);

            if (queriesOk && foreignKeysOk && indexesOk)
            {
                FinalizeSysMetadata(metadata, tables);
                return metadata;
            }

            metadata.Queries.Clear();
            metadata.ForeignKeys.Clear();
            metadata.Indexes.Clear();
            AccessApplicationSysReader.TryGrantAndReadMetadata(
                _options,
                _options.AccessDbUser,
                metadata.Queries,
                metadata.ForeignKeys,
                tableSet,
                _logger);

            if (metadata.Queries.Count == 0)
            {
                _logger.Information("Retrying query read via OleDb...");
                TryReadQueriesFromMsysStandalone(metadata.Queries);
            }

            if (metadata.ForeignKeys.Count == 0)
            {
                _logger.Information("Retrying FK read via OleDb...");
                var fkRetrySucceeded = TryReadForeignKeysStandalone(metadata.ForeignKeys, tableSet);
                if (fkRetrySucceeded && metadata.ForeignKeys.Count > 0)
                {
                    _logger.Information(
                        "Read {Count} relationships from MSysRelationships (after GRANT)",
                        metadata.ForeignKeys.Count);
                }
                else if (fkRetrySucceeded)
                {
                    _logger.Information("No Access relationships defined; proceeding without FK constraints");
                }
                else
                {
                    _logger.Warning("Could not read MSysRelationships; proceeding without FK constraints");
                }
            }

            if (metadata.Indexes.Count == 0)
            {
                _logger.Information("Retrying index read via OleDb...");
                var indexRetrySucceeded = TryReadIndexesStandalone(metadata.Indexes, tableSet);
                if (indexRetrySucceeded && metadata.Indexes.Count > 0)
                {
                    _logger.Information(
                        "Read {Count} indexes from MSysIndexes (after GRANT)",
                        metadata.Indexes.Count);
                }
                else if (indexRetrySucceeded)
                {
                    _logger.Information("No Access indexes returned from MSysIndexes; trying DAO fallback");
                    AccessApplicationSysReader.TryReadIndexesViaDao(
                        _options,
                        metadata.Indexes,
                        tableSet,
                        _logger);
                }
                else
                {
                    _logger.Warning("Could not read MSysIndexes; trying DAO fallback");
                    AccessApplicationSysReader.TryReadIndexesViaDao(
                        _options,
                        metadata.Indexes,
                        tableSet,
                        _logger);
                }
            }

            if (metadata.Queries.Count == 0 &&
                !new AccessViewConfigLoader(_options, _logger).TryLoadQueries(metadata.Queries))
            {
                using (var conn = _factory.Create())
                {
                    conn.Open();
                    LogUnreadableViews(conn);
                }
            }
            else if (metadata.Queries.Count > 0)
            {
                _logger.Information(
                    "Access queries ready: {Count} total, {Provision} marked for SQL Server views",
                    metadata.Queries.Count,
                    CountProvisionable(metadata.Queries));
            }

            FinalizeSysMetadata(metadata, tables);
            return metadata;
        }

        private void FinalizeSysMetadata(AccessSysMetadata metadata, IList<AccessTableSchema> tables)
        {
            metadata.ForeignKeys = TableExcludeFilter.FilterForeignKeys(
                metadata.ForeignKeys,
                _options.AccessSqlExcludeTables);

            metadata.Indexes = TableExcludeFilter.FilterIndexes(
                metadata.Indexes,
                _options.AccessSqlExcludeTables);

            ApplyPrimaryKeyColumns(tables, metadata.Indexes);
        }

        private static void ApplyPrimaryKeyColumns(IList<AccessTableSchema> tables, IList<AccessIndexInfo> indexes)
        {
            var primaryByTable = indexes
                .Where(i => i.IsPrimary && i.Columns.Count > 0)
                .GroupBy(i => i.TableName, StringComparer.OrdinalIgnoreCase)
                .ToDictionary(g => g.Key, g => g.First(), StringComparer.OrdinalIgnoreCase);

            foreach (var table in tables)
            {
                AccessIndexInfo primary;
                if (primaryByTable.TryGetValue(table.TableName, out primary))
                {
                    table.PrimaryKeyColumns = primary.Columns.ToList();
                    table.PrimaryKeyColumn = primary.Columns[0];
                }
                else if (!string.IsNullOrWhiteSpace(table.PrimaryKeyColumn))
                {
                    table.PrimaryKeyColumns = new List<string> { table.PrimaryKeyColumn };
                }
            }
        }

        private bool TryReadIndexesStandalone(IList<AccessIndexInfo> indexes, HashSet<string> tableSet)
        {
            indexes.Clear();
            using (var conn = _factory.Create())
            {
                conn.Open();
                return TryReadIndexesFromMSys(conn, indexes, tableSet, _logger);
            }
        }

        internal static bool TryReadIndexesFromMSys(
            OleDbConnection conn,
            IList<AccessIndexInfo> indexes,
            HashSet<string> tableSet,
            ILogger logger)
        {
            indexes.Clear();
            try
            {
                var sql = @"
SELECT t.Name AS TableName, i.Name AS IndexName, i.ColumnName, i.ColumnOrder,
       i.PrimaryKey, i.Unique
FROM MSysIndexes AS i
INNER JOIN MSysObjects AS t ON i.ID = t.Id
WHERE t.Type = 1
ORDER BY t.Name, i.Name, i.ColumnOrder";

                var rows = new List<AccessIndexGrouper.MsysIndexRow>();
                using (var cmd = new OleDbCommand(sql, conn))
                {
                    cmd.CommandTimeout = 5;
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            rows.Add(new AccessIndexGrouper.MsysIndexRow
                            {
                                TableName = Convert.ToString(reader["TableName"]),
                                IndexName = Convert.ToString(reader["IndexName"]),
                                ColumnName = Convert.ToString(reader["ColumnName"]),
                                ColumnOrder = reader["ColumnOrder"] == DBNull.Value
                                    ? 0
                                    : Convert.ToInt32(reader["ColumnOrder"]),
                                PrimaryKey = reader["PrimaryKey"] != DBNull.Value &&
                                    Convert.ToBoolean(reader["PrimaryKey"]),
                                Unique = reader["Unique"] != DBNull.Value &&
                                    Convert.ToBoolean(reader["Unique"])
                            });
                        }
                    }
                }

                foreach (var index in AccessIndexGrouper.GroupRows(rows, tableSet))
                {
                    indexes.Add(index);
                }

                return true;
            }
            catch (Exception ex)
            {
                logger?.Debug("MSysIndexes read failed: {Message}", ex.Message);
                return false;
            }
        }

        public IList<ForeignKeyInfo> ReadForeignKeys(IList<AccessTableSchema> tables)
        {
            return ReadSysMetadata(tables).ForeignKeys;
        }

        private bool TryReadForeignKeysStandalone(IList<ForeignKeyInfo> keys, HashSet<string> tableSet)
        {
            keys.Clear();
            using (var conn = _factory.Create())
            {
                conn.Open();
                return TryReadForeignKeysFromMSys(conn, keys, tableSet, _logger);
            }
        }

        private static bool TryReadForeignKeysFromMSys(
            OleDbConnection conn,
            IList<ForeignKeyInfo> keys,
            HashSet<string> tableSet,
            ILogger logger)
        {
            keys.Clear();
            try
            {
                var relSql = @"
SELECT szObject AS ChildTable, szColumn AS ChildColumn,
       szReferencedObject AS ParentTable, szReferencedColumn AS ParentColumn,
       szRelationship AS RelName, grbit
FROM MSysRelationships";
                using (var cmd = new OleDbCommand(relSql, conn))
                {
                    cmd.CommandTimeout = 5;
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var child = Convert.ToString(reader["ChildTable"]);
                            var parent = Convert.ToString(reader["ParentTable"]);
                            if (!tableSet.Contains(child) || !tableSet.Contains(parent))
                            {
                                continue;
                            }

                            var grbit = reader["grbit"] == DBNull.Value ? 0 : Convert.ToInt32(reader["grbit"]);
                            ParseRelationGrbit(grbit, out var onUpdateCascade, out var onDeleteCascade);

                            keys.Add(new ForeignKeyInfo
                            {
                                ChildTable = child,
                                ChildColumn = Convert.ToString(reader["ChildColumn"]),
                                ParentTable = parent,
                                ParentColumn = Convert.ToString(reader["ParentColumn"]),
                                ConstraintName = "FK_" + child + "_" + parent,
                                OnUpdateCascade = onUpdateCascade,
                                OnDeleteCascade = onDeleteCascade
                            });
                        }
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                logger?.Debug("MSysRelationships read failed: {Message}", ex.Message);
                return false;
            }
        }

        public IList<AccessQuerySchema> ReadQueries()
        {
            return ReadSysMetadata(new List<AccessTableSchema>()).Queries;
        }

        public IList<AccessQuerySchema> ReadQueries(IList<AccessTableSchema> tables)
        {
            return ReadSysMetadata(tables).Queries;
        }

        private bool TryReadQueriesFromMsysStandalone(IList<AccessQuerySchema> queries)
        {
            queries.Clear();
            using (var conn = _factory.Create())
            {
                conn.Open();
                return TryReadQueriesFromMSys(conn, queries);
            }
        }

        private static int CountProvisionable(IList<AccessQuerySchema> queries)
        {
            var count = 0;
            foreach (var query in queries)
            {
                if (query.ShouldProvision)
                {
                    count++;
                }
            }

            return count;
        }

        private bool TryReadQueriesFromMSys(OleDbConnection conn, IList<AccessQuerySchema> queries)
        {
            try
            {
                var sql = "SELECT [Name], [SQL] FROM MSysObjects WHERE [Type]=5 AND [Flags]=0";
                using (var cmd = new OleDbCommand(sql, conn))
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var name = Convert.ToString(reader["Name"]);
                        var sqlText = reader["SQL"] == DBNull.Value ? "" : Convert.ToString(reader["SQL"]);
                        var q = new AccessQuerySchema { Name = name, SqlText = sqlText };
                        ClassifyQuery(q);
                        queries.Add(q);
                    }
                }

                return queries.Count > 0;
            }
            catch (Exception ex)
            {
                _logger.Debug("MSysObjects read failed ({Message})", ex.Message);
                return false;
            }
        }

        private void LogUnreadableViews(OleDbConnection conn)
        {
            var views = conn.GetSchema("Tables")
                .AsEnumerable()
                .Where(r => string.Equals(Convert.ToString(r["TABLE_TYPE"]), "VIEW", StringComparison.OrdinalIgnoreCase))
                .Select(r => Convert.ToString(r["TABLE_NAME"]))
                .Where(n => !TableExcludeFilter.IsSystemTable(n))
                .ToList();

            if (views.Count == 0)
            {
                return;
            }

            _logger.Warning(
                "Could not read SQL for {Count} Access views — export query SQL to config\\access-views.json: {Names}",
                views.Count,
                string.Join(", ", views));
        }

        public static void ClassifyQuery(AccessQuerySchema query)
        {
            if (string.IsNullOrWhiteSpace(query.SqlText))
            {
                query.ShouldProvision = false;
                query.SkipReason = "empty SQL";
                return;
            }

            query.ShouldProvision = true;
            query.SkipReason = null;
        }

        private AccessTableSchema ReadTableSchema(OleDbConnection conn, string tableName)
        {
            var schema = new AccessTableSchema { TableName = tableName };
            var autoIncrementCount = 0;
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = "SELECT TOP 1 * FROM [" + tableName + "]";
                using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo))
                {
                    var table = reader.GetSchemaTable();
                    foreach (DataRow row in table.Rows)
                    {
                        var isAutoIncrement = row.Table.Columns.Contains("IsAutoIncrement") &&
                            row["IsAutoIncrement"] != DBNull.Value &&
                            Convert.ToBoolean(row["IsAutoIncrement"]);

                        if (isAutoIncrement)
                        {
                            autoIncrementCount++;
                        }

                        var col = new AccessColumnSchema
                        {
                            Name = Convert.ToString(row["ColumnName"]),
                            ClrType = (Type)row["DataType"],
                            ColumnSize = Convert.ToInt32(row["ColumnSize"]),
                            AllowNull = Convert.ToBoolean(row["AllowDBNull"]),
                            IsPrimaryKey = Convert.ToBoolean(row["IsKey"]),
                            IsAutoIncrement = isAutoIncrement
                        };
                        schema.Columns.Add(col);
                    }
                }
            }

            if (autoIncrementCount > 1)
            {
                _logger.Warning(
                    "Table {Table} has {Count} AutoNumber columns; only one IDENTITY column is supported per table",
                    tableName,
                    autoIncrementCount);
            }

            var pk = schema.Columns.FirstOrDefault(c => c.IsPrimaryKey);
            if (pk != null)
            {
                schema.PrimaryKeyColumn = pk.Name;
            }
            else
            {
                schema.PrimaryKeyColumn = schema.Columns
                    .Select(c => c.Name)
                    .FirstOrDefault(n => n.EndsWith("ID", StringComparison.OrdinalIgnoreCase));
            }

            return schema;
        }

        /// <summary>Enriches columns with DefaultValue and ValidationRule from DAO (COM fallback).</summary>
        public void EnrichColumnMetadataFromDao(IList<AccessTableSchema> tables)
        {
            var tableSet = new HashSet<string>(tables.Select(t => t.TableName), StringComparer.OrdinalIgnoreCase);
            AccessApplicationSysReader.TryReadFieldMetadataViaDao(_options, tables, tableSet, _logger);
        }

        public static IList<AccessTableSchema> SortByDependencies(
            IList<AccessTableSchema> tables,
            IList<ForeignKeyInfo> foreignKeys)
        {
            var names = tables.Select(t => t.TableName).ToList();
            var deps = new Dictionary<string, HashSet<string>>(StringComparer.OrdinalIgnoreCase);
            foreach (var name in names)
            {
                deps[name] = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
            }

            foreach (var fk in foreignKeys)
            {
                if (deps.ContainsKey(fk.ChildTable) && deps.ContainsKey(fk.ParentTable))
                {
                    deps[fk.ChildTable].Add(fk.ParentTable);
                }
            }

            var sorted = new List<AccessTableSchema>();
            var visited = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
            var visiting = new HashSet<string>(StringComparer.OrdinalIgnoreCase);

            Action<string> visit = null;
            visit = tableName =>
            {
                if (visited.Contains(tableName))
                {
                    return;
                }

                if (visiting.Contains(tableName))
                {
                    return;
                }

                visiting.Add(tableName);
                foreach (var parent in deps[tableName])
                {
                    visit(parent);
                }

                visiting.Remove(tableName);
                visited.Add(tableName);
                var schema = tables.First(t => string.Equals(t.TableName, tableName, StringComparison.OrdinalIgnoreCase));
                sorted.Add(schema);
            };

            foreach (var name in names)
            {
                visit(name);
            }

            return sorted;
        }

        internal const int RelationUpdateCascade = 256;
        internal const int RelationDeleteCascade = 4096;

        internal static void ParseRelationGrbit(int grbit, out bool onUpdateCascade, out bool onDeleteCascade)
        {
            onUpdateCascade = (grbit & RelationUpdateCascade) == RelationUpdateCascade;
            onDeleteCascade = (grbit & RelationDeleteCascade) == RelationDeleteCascade;
        }
    }
}