using System;
using System.Data.OleDb;
using System.IO;
using Microsoft.Data.SqlClient;
using MySqlConnector;
using SyncEngine.Configuration;

namespace SyncEngine.Sync
{
    public static class SyncErrorFormatter
    {
        public static string Describe(Exception ex)
        {
            if (ex == null)
            {
                return "Unknown error.";
            }

            if (ex is ConfigurationException configEx)
            {
                return configEx.Message;
            }

            // SSL/SSPI can wrap as MySqlException (often IsTransient) or appear deeper in the chain.
            if (IsSslHandshakeFailure(ex))
            {
                return "MySQL TLS handshake failed (Schannel/SSPI). " +
                       "SyncEngine requests TLS 1.2; on SBS 2011 / Server 2008 R2 ensure Schannel TLS 1.2 is available " +
                       "(SP1/updates). When a reboot is possible, enable TLS 1.2 Client in Schannel registry. " +
                       "Check MYSQL_HOST reachability only after TLS succeeds.";
            }

            var oleDb = FindException<OleDbException>(ex);
            if (oleDb != null)
            {
                return DescribeOleDb(oleDb);
            }

            var sql = FindException<SqlException>(ex);
            if (sql != null)
            {
                return DescribeSql(sql);
            }

            var mysql = FindException<MySqlException>(ex);
            if (mysql != null)
            {
                return DescribeMySql(mysql);
            }

            if (ex is FileNotFoundException)
            {
                return ex.Message;
            }

            return ex.Message;
        }

        private static string DescribeOleDb(OleDbException ex)
        {
            var message = ex.Message ?? string.Empty;
            if (message.IndexOf("not registered", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                return "Microsoft Access Database Engine (ACE OLEDB 12.0) is not installed or the wrong bitness is installed. " +
                       "Install ACE and ensure the SyncEngine build platform (x64/x86) matches.";
            }

            if (message.IndexOf("Not a valid password", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                return "Access database rejected the password. Check ACCESS_DB_PASSWORD in .env.";
            }

            if (message.IndexOf("cannot find", StringComparison.OrdinalIgnoreCase) >= 0 ||
                message.IndexOf("could not find", StringComparison.OrdinalIgnoreCase) >= 0 ||
                message.IndexOf("could not use", StringComparison.OrdinalIgnoreCase) >= 0 ||
                message.IndexOf("file already in use", StringComparison.OrdinalIgnoreCase) >= 0)
            {
                if (message.IndexOf("file already in use", StringComparison.OrdinalIgnoreCase) >= 0)
                {
                    return "Access database is locked (file already in use). Close Microsoft Access or wait for a prior sync to finish.";
                }

                return "Access database file could not be opened. Check ACCESS_DB_PATH in .env.";
            }

            return "Access database error: " + message;
        }

        private static string DescribeSql(SqlException ex)
        {
            if (ex.Number == -1 || ex.Number == 2 || ex.Number == 53)
            {
                return "Cannot connect to SQL Express. Check SQL_EXPRESS_CONNECTION_STRING, that the service is running, and firewall/VPN settings.";
            }

            if (ex.Number == 18456)
            {
                return "SQL Express login failed. Check credentials in SQL_EXPRESS_CONNECTION_STRING.";
            }

            if (ex.Number == 4060)
            {
                return "SQL Express database not found. Check SQL_EXPRESS_DATABASE or create the database first.";
            }

            return "SQL Express error (" + ex.Number + "): " + ex.Message;
        }

        private static string DescribeMySql(MySqlException ex)
        {
            if (ex.IsTransient)
            {
                return "Temporary MySQL connection failure. Check MYSQL_HOST, MYSQL_PORT, and network connectivity.";
            }

            if (ex.ErrorCode == MySqlErrorCode.AccessDenied)
            {
                return "MySQL login failed. Check MYSQL_USER and MYSQL_PASSWORD in .env.";
            }

            if (ex.ErrorCode == MySqlErrorCode.UnknownDatabase)
            {
                return "MySQL database not found. Check MYSQL_DATABASE in .env.";
            }

            return "MySQL error (" + ex.ErrorCode + "): " + ex.Message;
        }

        /// <summary>True when the failure is a TLS/Schannel handshake (not a plain network blip).</summary>
        internal static bool IsSslHandshakeFailure(Exception ex)
        {
            var current = ex;
            while (current != null)
            {
                var message = current.Message ?? string.Empty;
                if (message.IndexOf("SSL Authentication", StringComparison.OrdinalIgnoreCase) >= 0 ||
                    message.IndexOf("SSPI", StringComparison.OrdinalIgnoreCase) >= 0 ||
                    message.IndexOf("function requested is not supported", StringComparison.OrdinalIgnoreCase) >= 0)
                {
                    return true;
                }

                if (current.GetType().FullName == "System.Security.Authentication.AuthenticationException")
                {
                    return true;
                }

                current = current.InnerException;
            }

            return false;
        }

        private static T FindException<T>(Exception ex) where T : Exception
        {
            var current = ex;
            while (current != null)
            {
                var typed = current as T;
                if (typed != null)
                {
                    return typed;
                }

                current = current.InnerException;
            }

            return null;
        }
    }
}