using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Globalization;
using System.Linq;
using Microsoft.Data.SqlClient;
using Serilog;
using SyncEngine.Access;
using SyncEngine.Configuration;

namespace SyncEngine.SqlServer
{
    /// <summary>
    /// Handles unique secondary-key collisions after INSERT error 2601/2627.
    /// Must not be called on the happy path (no per-row probes).
    /// </summary>
    internal sealed class UniqueKeyCollisionResolver
    {
        private readonly ILogger _logger;
        private readonly int _commandTimeoutSeconds;

        public UniqueKeyCollisionResolver(ILogger logger, int commandTimeoutSeconds)
        {
            _logger = logger;
            _commandTimeoutSeconds = commandTimeoutSeconds;
        }

        public static bool IsUniqueIndexViolation(SqlException ex)
        {
            if (ex == null)
            {
                return false;
            }

            if (UniqueKeyCollisionClassifier.IsUniqueIndexViolation(ex.Number))
            {
                return true;
            }

            foreach (SqlError err in ex.Errors)
            {
                if (UniqueKeyCollisionClassifier.IsUniqueIndexViolation(err.Number))
                {
                    return true;
                }
            }

            return false;
        }

        public bool TryResolve(
            SqlConnection sqlConn,
            SqlTransaction tx,
            OleDbConnection accessConn,
            string qualifiedTable,
            AccessTableSchema table,
            IList<string> pkColumns,
            IList<AccessIndexInfo> uniqueIndexes,
            Dictionary<string, object> incomingRow)
        {
            if (uniqueIndexes == null || uniqueIndexes.Count == 0 || pkColumns == null || pkColumns.Count == 0)
            {
                return false;
            }

            EnsureTransactionUsable(sqlConn, tx);

            string uniqueCol = null;
            Dictionary<string, object> occupyingSql = null;
            foreach (var index in uniqueIndexes)
            {
                var col = index.Columns[0];
                object incomingValue;
                if (!incomingRow.TryGetValue(col, out incomingValue) || incomingValue == null || incomingValue is DBNull)
                {
                    continue;
                }

                occupyingSql = FindSqlRowByUnique(sqlConn, tx, qualifiedTable, pkColumns, table, col, incomingValue);
                if (occupyingSql != null)
                {
                    uniqueCol = col;
                    break;
                }
            }

            if (occupyingSql == null || uniqueCol == null)
            {
                return false;
            }

            var occupyingPkKey = DataSyncPrimaryKeyHelper.BuildPkKey(occupyingSql, pkColumns);
            var incomingPkKey = DataSyncPrimaryKeyHelper.BuildPkKey(incomingRow, pkColumns);
            if (string.Equals(occupyingPkKey, incomingPkKey, StringComparison.OrdinalIgnoreCase))
            {
                return false;
            }

            var accessRow = FindAccessRowByPk(accessConn, table, pkColumns, occupyingSql);
            var occupyingExistsInAccess = accessRow != null;
            object accessCodeObj = null;
            var occupyingAccessCode = occupyingExistsInAccess && accessRow.TryGetValue(uniqueCol, out accessCodeObj)
                ? Convert.ToString(accessCodeObj, CultureInfo.InvariantCulture)
                : null;

            object incomingCodeObj;
            incomingRow.TryGetValue(uniqueCol, out incomingCodeObj);
            var incomingCode = Convert.ToString(incomingCodeObj, CultureInfo.InvariantCulture);

            var occupyingDescription = ReadDescription(occupyingSql);
            var incomingDescription = ReadDescription(incomingRow);

            var action = UniqueKeyCollisionClassifier.Classify(
                occupyingExistsInAccess,
                occupyingAccessCode,
                incomingCode,
                occupyingDescription,
                incomingDescription);

            _logger.Information(
                "Unique {Column} collision on {Table}: incoming PK {IncomingPk} occupying PK {OccupyingPk} action={Action}",
                uniqueCol,
                table.TableName,
                incomingPkKey,
                occupyingPkKey,
                action);

            switch (action)
            {
                case UniqueKeyCollisionAction.FailDuplicate:
                    throw new InvalidOperationException(string.Format(
                        CultureInfo.InvariantCulture,
                        "Access has two rows with {0}={1} (incoming PK {2}, occupying PK {3}).",
                        uniqueCol,
                        incomingCode,
                        incomingPkKey,
                        occupyingPkKey));
                case UniqueKeyCollisionAction.StaleUpdateOccupying:
                    if (accessRow == null)
                    {
                        return false;
                    }

                    UpdateUniqueColumn(sqlConn, tx, qualifiedTable, pkColumns, occupyingSql, uniqueCol, accessCodeObj);
                    return true;
                case UniqueKeyCollisionAction.Remap:
                    RenameOccupyingUnique(sqlConn, tx, qualifiedTable, pkColumns, occupyingSql, uniqueCol, incomingCode, occupyingPkKey, table);
                    RetargetChildren(sqlConn, tx, table, pkColumns, occupyingSql, incomingRow);
                    return true;
                case UniqueKeyCollisionAction.Recycle:
                    RenameOccupyingUnique(sqlConn, tx, qualifiedTable, pkColumns, occupyingSql, uniqueCol, incomingCode, occupyingPkKey, table);
                    return true;
                default:
                    return false;
            }
        }

        private void EnsureTransactionUsable(SqlConnection sqlConn, SqlTransaction tx)
        {
            using (var cmd = new SqlCommand("SELECT XACT_STATE()", sqlConn, tx))
            {
                cmd.CommandTimeout = _commandTimeoutSeconds;
                var state = Convert.ToInt32(cmd.ExecuteScalar(), CultureInfo.InvariantCulture);
                if (state == -1)
                {
                    throw new InvalidOperationException(
                        "SQL transaction is unusable after unique-index error (XACT_STATE = -1).");
                }
            }
        }

        private Dictionary<string, object> FindSqlRowByUnique(
            SqlConnection sqlConn,
            SqlTransaction tx,
            string qualifiedTable,
            IList<string> pkColumns,
            AccessTableSchema table,
            string uniqueCol,
            object uniqueValue)
        {
            var selectCols = new List<string>(pkColumns);
            if (!selectCols.Contains(uniqueCol, StringComparer.OrdinalIgnoreCase))
            {
                selectCols.Add(uniqueCol);
            }

            if (HasColumn(table, "strDescription") &&
                !selectCols.Contains("strDescription", StringComparer.OrdinalIgnoreCase))
            {
                selectCols.Add("strDescription");
            }

            var sql = string.Format(
                CultureInfo.InvariantCulture,
                "SELECT TOP 1 {0} FROM {1} WHERE [{2}] = @v",
                string.Join(", ", selectCols.Select(c => "[" + c + "]")),
                qualifiedTable,
                uniqueCol);

            using (var cmd = new SqlCommand(sql, sqlConn, tx))
            {
                cmd.CommandTimeout = _commandTimeoutSeconds;
                cmd.Parameters.AddWithValue("@v", uniqueValue);
                using (var reader = cmd.ExecuteReader())
                {
                    if (!reader.Read())
                    {
                        return null;
                    }

                    var row = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);
                    for (var i = 0; i < reader.FieldCount; i++)
                    {
                        row[reader.GetName(i)] = reader.IsDBNull(i) ? null : reader.GetValue(i);
                    }

                    return row;
                }
            }
        }

        private static Dictionary<string, object> FindAccessRowByPk(
            OleDbConnection accessConn,
            AccessTableSchema table,
            IList<string> pkColumns,
            Dictionary<string, object> occupyingSql)
        {
            if (accessConn == null)
            {
                return null;
            }

            var predicates = new List<string>();
            for (var i = 0; i < pkColumns.Count; i++)
            {
                predicates.Add("[" + pkColumns[i] + "] = ?");
            }

            var sql = string.Format(
                CultureInfo.InvariantCulture,
                "SELECT TOP 1 * FROM [{0}] WHERE {1}",
                table.TableName,
                string.Join(" AND ", predicates));

            using (var cmd = new OleDbCommand(sql, accessConn))
            {
                cmd.CommandTimeout = 30;
                for (var i = 0; i < pkColumns.Count; i++)
                {
                    object value;
                    occupyingSql.TryGetValue(pkColumns[i], out value);
                    cmd.Parameters.AddWithValue("p" + i, value ?? DBNull.Value);
                }

                using (var reader = cmd.ExecuteReader())
                {
                    if (!reader.Read())
                    {
                        return null;
                    }

                    var row = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);
                    for (var i = 0; i < reader.FieldCount; i++)
                    {
                        row[reader.GetName(i)] = reader.IsDBNull(i) ? null : reader.GetValue(i);
                    }

                    return row;
                }
            }
        }

        private void RenameOccupyingUnique(
            SqlConnection sqlConn,
            SqlTransaction tx,
            string qualifiedTable,
            IList<string> pkColumns,
            Dictionary<string, object> occupyingSql,
            string uniqueCol,
            string originalCode,
            string occupyingPkKey,
            AccessTableSchema table)
        {
            var maxLen = UniqueColumnMaxLength(table, uniqueCol);
            var renamed = UniqueKeyCollisionClassifier.BuildRemovedCode(originalCode, maxLen, occupyingPkKey);
            try
            {
                UpdateUniqueColumn(sqlConn, tx, qualifiedTable, pkColumns, occupyingSql, uniqueCol, renamed);
            }
            catch (SqlException ex) when (IsUniqueIndexViolation(ex))
            {
                var fallback = UniqueKeyCollisionClassifier.BuildPkFallback(maxLen, occupyingPkKey);
                UpdateUniqueColumn(sqlConn, tx, qualifiedTable, pkColumns, occupyingSql, uniqueCol, fallback);
            }
        }

        private void UpdateUniqueColumn(
            SqlConnection sqlConn,
            SqlTransaction tx,
            string qualifiedTable,
            IList<string> pkColumns,
            Dictionary<string, object> occupyingSql,
            string uniqueCol,
            object newValue)
        {
            var where = DataSyncPrimaryKeyHelper.BuildSqlWhereClause(pkColumns, "@pk");
            var sql = string.Format(
                CultureInfo.InvariantCulture,
                "UPDATE {0} SET [{1}] = @v WHERE {2}",
                qualifiedTable,
                uniqueCol,
                where);

            using (var cmd = new SqlCommand(sql, sqlConn, tx))
            {
                cmd.CommandTimeout = _commandTimeoutSeconds;
                cmd.Parameters.AddWithValue("@v", newValue ?? (object)DBNull.Value);
                DataSyncPrimaryKeyHelper.BindPrimaryKeyParameters(cmd, occupyingSql, pkColumns, "@pk");
                cmd.ExecuteNonQuery();
            }
        }

        private void RetargetChildren(
            SqlConnection sqlConn,
            SqlTransaction tx,
            AccessTableSchema table,
            IList<string> pkColumns,
            Dictionary<string, object> occupyingSql,
            Dictionary<string, object> incomingRow)
        {
            if (pkColumns.Count != 1)
            {
                return;
            }

            var pkCol = pkColumns[0];
            var refCol = UniqueKeyCollisionClassifier.BuildRefColumnName(pkCol);
            object oldId;
            object newId;
            occupyingSql.TryGetValue(pkCol, out oldId);
            incomingRow.TryGetValue(pkCol, out newId);
            if (oldId == null || newId == null)
            {
                return;
            }

            const string findSql = @"
SELECT QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS QualifiedTable
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.is_ms_shipped = 0
  AND c.name = @col
  AND t.name <> @skipTable";

            var children = new List<string>();
            using (var cmd = new SqlCommand(findSql, sqlConn, tx))
            {
                cmd.CommandTimeout = _commandTimeoutSeconds;
                cmd.Parameters.AddWithValue("@col", refCol);
                cmd.Parameters.AddWithValue("@skipTable", table.TableName);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        children.Add(reader.GetString(0));
                    }
                }
            }

            foreach (var child in children)
            {
                var sql = string.Format(
                    CultureInfo.InvariantCulture,
                    "UPDATE {0} SET [{1}] = @newId WHERE [{1}] = @oldId",
                    child,
                    refCol);
                using (var cmd = new SqlCommand(sql, sqlConn, tx))
                {
                    cmd.CommandTimeout = _commandTimeoutSeconds;
                    cmd.Parameters.AddWithValue("@newId", newId);
                    cmd.Parameters.AddWithValue("@oldId", oldId);
                    var count = cmd.ExecuteNonQuery();
                    if (count > 0)
                    {
                        _logger.Information(
                            "Retargeted {Count} {Child}.{RefCol} rows from {OldId} to {NewId}",
                            count,
                            child,
                            refCol,
                            oldId,
                            newId);
                    }
                }
            }
        }

        private static string ReadDescription(IReadOnlyDictionary<string, object> row)
        {
            object value;
            if (row != null && row.TryGetValue("strDescription", out value) && value != null && !(value is DBNull))
            {
                return Convert.ToString(value, CultureInfo.InvariantCulture);
            }

            return null;
        }

        private static bool HasColumn(AccessTableSchema table, string name)
        {
            return table?.Columns != null &&
                table.Columns.Any(c => string.Equals(c.Name, name, StringComparison.OrdinalIgnoreCase));
        }

        private static int UniqueColumnMaxLength(AccessTableSchema table, string uniqueCol)
        {
            if (table?.Columns == null)
            {
                return 20;
            }

            foreach (var col in table.Columns)
            {
                if (string.Equals(col.Name, uniqueCol, StringComparison.OrdinalIgnoreCase) && col.ColumnSize > 0)
                {
                    return col.ColumnSize;
                }
            }

            return 20;
        }
    }
}