using System;
using System.Collections.Generic;
using System.IO;
using System.Reflection;
using System.Text.RegularExpressions;
using Microsoft.Win32;
using Serilog;
using SyncEngine.Configuration;

namespace SyncEngine.Access
{
    /// <summary>
    /// Opens Access metadata via full Access.Application when available; on Access Runtime
    /// (CreateInstance fails with CO_E_SERVER_EXEC_FAILURE) opens the backend with
    /// DAO.DBEngine.OpenDatabase + ;PWD=… — no MSACCESS.EXE UI, so no modal dialogs.
    /// Note: MSACCESS /pwd is workgroup account password only — never the database encrypt password.
    /// </summary>
    internal static class AccessApplicationSysReader
    {
        public static void TryGrantAndReadMetadata(
            SyncOptions options,
            string configuredUser,
            IList<AccessQuerySchema> queries,
            IList<ForeignKeyInfo> keys,
            HashSet<string> tableSet,
            ILogger logger)
        {
            object app = null;
            object db = null;
            object engine = null;
            try
            {
                logger.Information("Opening Access for GRANT and query export...");
                if (!TryOpenAccessApplication(options, logger, out app, out db))
                {
                    logger.Warning(
                        "Access.Application unavailable; opening via DAO.DBEngine (no MSACCESS UI)...");
                    OpenDaoDatabase(options, out engine, out db);
                    logger.Information("Skipping MSys GRANT on DAO-only path (requires Access.Application).");
                }
                else
                {
                    var grantUser = ResolveGrantUser(app, configuredUser);
                    logger.Information("Granting MSys access to {User}...", grantUser);
                    var connection = GetProjectConnection(app);
                    TryGrant(connection, grantUser, logger);
                    AccessDaoHelper.ReleaseCom(connection);
                }

                logger.Information("Reading saved queries from QueryDefs...");
                AccessDaoHelper.ReadQueryDefs(db, queries, logger);
                logger.Information("Read {Count} QueryDefs", queries.Count);

                logger.Information("Reading table relationships from Relations...");
                AccessDaoHelper.ReadRelations(db, keys, tableSet, logger);
                logger.Information("Read {Count} relationships", keys.Count);
            }
            catch (Exception ex)
            {
                logger.Warning(
                    "Access metadata step failed ({Message})",
                    AccessDaoHelper.FormatException(ex));
            }
            finally
            {
                CloseDaoSession(app, db, engine, logger);
            }
        }

        /// <summary>Reads field DefaultValue and ValidationRule via DAO.</summary>
        public static void TryReadFieldMetadataViaDao(
            SyncOptions options,
            IList<AccessTableSchema> tables,
            HashSet<string> tableSet,
            ILogger logger)
        {
            object app = null;
            object db = null;
            object engine = null;
            try
            {
                logger.Information("Opening Access for field metadata (defaults, validation)...");
                if (!TryOpenAccessApplication(options, logger, out app, out db))
                {
                    logger.Warning(
                        "Access.Application unavailable; opening via DAO.DBEngine (no MSACCESS UI)...");
                    OpenDaoDatabase(options, out engine, out db);
                }

                AccessDaoHelper.ReadFieldMetadata(db, tables, tableSet, logger);
            }
            catch (Exception ex)
            {
                logger.Warning(
                    "Access field metadata read failed ({Message})",
                    AccessDaoHelper.FormatException(ex));
            }
            finally
            {
                CloseDaoSession(app, db, engine, null);
            }
        }

        /// <summary>Reads TableDefs.Indexes when OleDb MSysIndexes is unavailable.</summary>
        public static void TryReadIndexesViaDao(
            SyncOptions options,
            IList<AccessIndexInfo> indexes,
            HashSet<string> tableSet,
            ILogger logger)
        {
            object app = null;
            object db = null;
            object engine = null;
            try
            {
                logger.Information("Opening Access for TableDefs.Indexes fallback...");
                if (!TryOpenAccessApplication(options, logger, out app, out db))
                {
                    logger.Warning(
                        "Access.Application unavailable; opening via DAO.DBEngine (no MSACCESS UI)...");
                    OpenDaoDatabase(options, out engine, out db);
                }

                AccessDaoHelper.ReadTableIndexes(db, indexes, tableSet, logger);
                logger.Information("Read {Count} indexes from TableDefs", indexes.Count);
            }
            catch (Exception ex)
            {
                logger.Warning(
                    "Access index read failed ({Message})",
                    AccessDaoHelper.FormatException(ex));
            }
            finally
            {
                CloseDaoSession(app, db, engine, null);
            }
        }

        private static bool TryOpenAccessApplication(
            SyncOptions options,
            ILogger logger,
            out object app,
            out object db)
        {
            app = null;
            db = null;
            var appType = Type.GetTypeFromProgID("Access.Application");
            if (appType == null)
            {
                return false;
            }

            try
            {
                app = OpenViaCreateInstance(appType, options);
                db = InvokeCurrentDb(app);
                return true;
            }
            catch (Exception ex)
            {
                logger.Debug(
                    "Access.Application CreateInstance/OpenCurrentDatabase failed ({Message})",
                    AccessDaoHelper.FormatException(ex));
                CloseApplication(app);
                app = null;
                AccessDaoHelper.ReleaseCom(db);
                db = null;
                return false;
            }
        }

        private static void OpenDaoDatabase(SyncOptions options, out object engine, out object db)
        {
            if (string.IsNullOrWhiteSpace(options.AccessDbPath) || !File.Exists(options.AccessDbPath))
            {
                throw new InvalidOperationException("ACCESS_DB_PATH is missing or not found for DAO open.");
            }

            var engineType = Type.GetTypeFromProgID("DAO.DBEngine.120")
                ?? Type.GetTypeFromProgID("DAO.DBEngine.36");
            if (engineType == null)
            {
                throw new InvalidOperationException(
                    "DAO.DBEngine is not registered (install ACE / Access Runtime).");
            }

            engine = Activator.CreateInstance(engineType);
            var connect = string.IsNullOrEmpty(options.AccessDbPassword)
                ? string.Empty
                : ";PWD=" + options.AccessDbPassword;

            try
            {
                // OpenDatabase(Name, Exclusive, ReadOnly, Connect)
                db = engineType.InvokeMember(
                    "OpenDatabase",
                    BindingFlags.InvokeMethod,
                    null,
                    engine,
                    new object[] { options.AccessDbPath, false, false, connect });
            }
            catch
            {
                AccessDaoHelper.ReleaseCom(engine);
                engine = null;
                throw;
            }
        }

        private static void CloseDaoSession(object app, object db, object engine, ILogger logger)
        {
            if (app != null)
            {
                AccessDaoHelper.ReleaseCom(db);
                if (logger != null)
                {
                    logger.Information("Closing Access.Application...");
                }

                CloseApplication(app);
                if (logger != null)
                {
                    logger.Information("Access.Application closed.");
                }

                return;
            }

            if (db != null)
            {
                try
                {
                    db.GetType().InvokeMember(
                        "Close",
                        BindingFlags.InvokeMethod,
                        null,
                        db,
                        null);
                }
                catch
                {
                    // ignore
                }

                AccessDaoHelper.ReleaseCom(db);
            }

            AccessDaoHelper.ReleaseCom(engine);
        }

        private static string ResolveGrantUser(object app, string configuredUser)
        {
            if (!string.IsNullOrWhiteSpace(configuredUser) &&
                !string.Equals(configuredUser, "current", StringComparison.OrdinalIgnoreCase))
            {
                return configuredUser.Trim();
            }

            var appType = app.GetType();
            try
            {
                return Convert.ToString(appType.InvokeMember(
                    "CurrentUser",
                    BindingFlags.InvokeMethod,
                    null,
                    app,
                    null));
            }
            catch (Exception)
            {
                return Convert.ToString(appType.InvokeMember(
                    "CurrentUser",
                    BindingFlags.GetProperty,
                    null,
                    app,
                    null));
            }
        }

        private static object InvokeCurrentDb(object app)
        {
            var appType = app.GetType();
            try
            {
                return appType.InvokeMember(
                    "CurrentDb",
                    BindingFlags.InvokeMethod,
                    null,
                    app,
                    null);
            }
            catch (Exception)
            {
                return appType.InvokeMember(
                    "CurrentDb",
                    BindingFlags.GetProperty,
                    null,
                    app,
                    null);
            }
        }

        private static void TryGrant(object connection, string userName, ILogger logger)
        {
            foreach (var table in new[] { "MSysObjects", "MSysRelationships", "MSysIndexes" })
            {
                try
                {
                    var ddl = string.Format("GRANT SELECT ON {0} TO [{1}];", table, userName);
                    connection.GetType().InvokeMember(
                        "Execute",
                        BindingFlags.InvokeMethod,
                        null,
                        connection,
                        new object[] { ddl });
                    logger.Information(
                        "Granted SELECT on {Table} to {User} (via Access.Application)",
                        table,
                        userName);
                }
                catch (Exception ex)
                {
                    logger.Debug(
                        "GRANT SELECT on {Table} via Access.Application ({Message})",
                        table,
                        AccessDaoHelper.FormatException(ex));
                }
            }
        }

        private static object OpenViaCreateInstance(Type appType, SyncOptions options)
        {
            var app = Activator.CreateInstance(appType);
            try
            {
                ConfigureHeadless(app);
                OpenCurrentDatabase(app, options);
                return app;
            }
            catch
            {
                CloseApplication(app);
                throw;
            }
        }

        private static void ConfigureHeadless(object app)
        {
            try
            {
                app.GetType().InvokeMember(
                    "Visible",
                    BindingFlags.SetProperty,
                    null,
                    app,
                    new object[] { false });
            }
            catch
            {
                // Runtime may reject Visible; continue
            }

            try
            {
                app.GetType().InvokeMember(
                    "UserControl",
                    BindingFlags.SetProperty,
                    null,
                    app,
                    new object[] { false });
            }
            catch
            {
                // Runtime may reject UserControl; continue
            }
        }

        private static void OpenCurrentDatabase(object app, SyncOptions options)
        {
            app.GetType().InvokeMember(
                "OpenCurrentDatabase",
                BindingFlags.InvokeMethod,
                null,
                app,
                new object[]
                {
                    options.AccessDbPath,
                    false,
                    string.IsNullOrEmpty(options.AccessDbPassword) ? Type.Missing : (object)options.AccessDbPassword
                });
        }

        /// <summary>Shell /user value: ACCESS_DB_USER when set; otherwise Admin.</summary>
        internal static string ResolveShellUser(string accessDbUser)
        {
            if (string.IsNullOrWhiteSpace(accessDbUser) ||
                string.Equals(accessDbUser, "current", StringComparison.OrdinalIgnoreCase))
            {
                return "Admin";
            }

            return accessDbUser.Trim();
        }

        /// <summary>
        /// Builds MSACCESS.EXE args if Shell is ever needed: stub path + /user.
        /// /pwd is workgroup account password only — never the database encrypt password.
        /// </summary>
        internal static string BuildShellArguments(string stubDbPath, string user, string workgroupPassword)
        {
            var args = "\"" + stubDbPath + "\" /user " + QuoteShellArg(user ?? "Admin");
            if (!string.IsNullOrEmpty(workgroupPassword))
            {
                args += " /pwd " + QuoteShellArg(workgroupPassword);
            }

            return args;
        }

        internal static string QuoteShellArg(string value)
        {
            if (value == null)
            {
                return "\"\"";
            }

            if (value.IndexOfAny(new[] { ' ', '\t', '"' }) < 0)
            {
                return value;
            }

            return "\"" + value.Replace("\"", "\\\"") + "\"";
        }

        internal static string MaskPasswordArgs(string args, string password)
        {
            if (string.IsNullOrEmpty(password) || string.IsNullOrEmpty(args))
            {
                return args;
            }

            return args.Replace(password, "***");
        }

        internal static string TryExtractExeFromOpenCommand(string command)
        {
            if (string.IsNullOrWhiteSpace(command))
            {
                return null;
            }

            var quoted = Regex.Match(command, "\"([^\"]*MSACCESS\\.EXE)\"", RegexOptions.IgnoreCase);
            if (quoted.Success)
            {
                return quoted.Groups[1].Value;
            }

            var unquoted = Regex.Match(command, @"(?i)([A-Za-z]:\\[^\s]*MSACCESS\.EXE)");
            if (unquoted.Success)
            {
                return unquoted.Groups[1].Value;
            }

            return null;
        }

        internal static string ResolveMsAccessExe()
        {
            foreach (var hive in new[] { RegistryHive.LocalMachine })
            {
                foreach (var view in new[] { RegistryView.Registry64, RegistryView.Registry32 })
                {
                    try
                    {
                        using (var baseKey = RegistryKey.OpenBaseKey(hive, view))
                        using (var classes = baseKey.OpenSubKey(@"Software\Classes"))
                        {
                            if (classes == null)
                            {
                                continue;
                            }

                            var exe = ResolveMsAccessExeFromClasses(classes);
                            if (!string.IsNullOrEmpty(exe) && File.Exists(exe))
                            {
                                return exe;
                            }
                        }
                    }
                    catch
                    {
                        // try next view
                    }
                }
            }

            foreach (var candidate in WellKnownMsAccessPaths())
            {
                if (File.Exists(candidate))
                {
                    return candidate;
                }
            }

            return null;
        }

        private static string ResolveMsAccessExeFromClasses(RegistryKey classes)
        {
            using (var curVerKey = classes.OpenSubKey(@"Access.Application\CurVer"))
            {
                if (curVerKey == null)
                {
                    return null;
                }

                var curVer = curVerKey.GetValue(null) as string;
                if (string.IsNullOrWhiteSpace(curVer))
                {
                    return null;
                }

                using (var cmdKey = classes.OpenSubKey(curVer + @"\shell\Open\command"))
                {
                    if (cmdKey == null)
                    {
                        return null;
                    }

                    return TryExtractExeFromOpenCommand(cmdKey.GetValue(null) as string);
                }
            }
        }

        private static IEnumerable<string> WellKnownMsAccessPaths()
        {
            var pf = Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles);
            var pf86 = Environment.GetFolderPath(Environment.SpecialFolder.ProgramFilesX86);
            yield return Path.Combine(pf, @"Microsoft Office\root\Office16\MSACCESS.EXE");
            yield return Path.Combine(pf, @"Microsoft Office\Office16\MSACCESS.EXE");
            if (!string.IsNullOrEmpty(pf86))
            {
                yield return Path.Combine(pf86, @"Microsoft Office\root\Office16\MSACCESS.EXE");
                yield return Path.Combine(pf86, @"Microsoft Office\Office16\MSACCESS.EXE");
            }
        }

        private static object GetProjectConnection(object app)
        {
            var currentProject = app.GetType().InvokeMember(
                "CurrentProject",
                BindingFlags.GetProperty,
                null,
                app,
                null);

            var connection = currentProject.GetType().InvokeMember(
                "Connection",
                BindingFlags.GetProperty,
                null,
                currentProject,
                null);

            AccessDaoHelper.ReleaseCom(currentProject);
            return connection;
        }

        private static void CloseApplication(object app)
        {
            if (app == null)
            {
                return;
            }

            try
            {
                app.GetType().InvokeMember(
                    "Quit",
                    BindingFlags.InvokeMethod,
                    null,
                    app,
                    new object[] { 1 });
            }
            catch
            {
                // ignore quit errors during cleanup
            }

            try
            {
                app.GetType().InvokeMember(
                    "CloseCurrentDatabase",
                    BindingFlags.InvokeMethod,
                    null,
                    app,
                    null);
            }
            catch
            {
                // ignore close errors during cleanup
            }

            AccessDaoHelper.ReleaseCom(app);
        }
    }
}