using System;
using System.Globalization;
using System.Text;
using System.Text.RegularExpressions;

namespace SyncEngine.MySql
{
    public static class ProductFieldMapper
    {
        public static string MakeSlug(int sqlExpressProductId, string code, string description)
        {
            var safeCode = UrlTitle((code ?? string.Empty).Trim().Replace("*", ""));
            var title = UrlTitle(description ?? string.Empty);
            var id = sqlExpressProductId.ToString(CultureInfo.InvariantCulture);

            return id + "-" + safeCode + "-" + title;
        }

        public static string UrlTitle(string value)
        {
            if (string.IsNullOrWhiteSpace(value))
            {
                return string.Empty;
            }

            var normalized = RemoveDiacritics(value.Trim());
            normalized = normalized.ToLowerInvariant();
            normalized = Regex.Replace(normalized, @"\s+", "-");
            normalized = Regex.Replace(normalized, @"[^a-z0-9_-]", "");
            normalized = Regex.Replace(normalized, @"-+", "-").Trim('-');
            return normalized;
        }

        private static string RemoveDiacritics(string value)
        {
            var normalized = value.Normalize(NormalizationForm.FormD);
            var builder = new StringBuilder(normalized.Length);
            foreach (var ch in normalized)
            {
                if (CharUnicodeInfo.GetUnicodeCategory(ch) != UnicodeCategory.NonSpacingMark)
                {
                    builder.Append(ch);
                }
            }

            return builder.ToString().Normalize(NormalizationForm.FormC);
        }

        public static string Truncate(string value, int maxLength)
        {
            if (string.IsNullOrEmpty(value) || value.Length <= maxLength)
            {
                return value;
            }

            return value.Substring(0, maxLength);
        }

        /// <summary>Trimmed text for required fields (e.g. description). Empty string is preserved.</summary>
        public static string TrimRequiredString(string value)
        {
            return (value ?? string.Empty).Trim();
        }

        /// <summary>Optional lookup/text fields: whitespace-only becomes null for MySQL NULL semantics.</summary>
        public static string NormalizeOptionalString(string value)
        {
            if (string.IsNullOrWhiteSpace(value))
            {
                return null;
            }

            return value.Trim();
        }

        public static decimal? ToPrice(object value)
        {
            if (value == null || value == DBNull.Value)
            {
                return null;
            }

            decimal parsed;
            if (value is decimal decimalValue)
            {
                parsed = decimalValue;
            }
            else if (value is double doubleValue)
            {
                parsed = (decimal)doubleValue;
            }
            else if (value is float floatValue)
            {
                parsed = (decimal)floatValue;
            }
            else if (!decimal.TryParse(
                Convert.ToString(value, CultureInfo.InvariantCulture),
                NumberStyles.Any,
                CultureInfo.InvariantCulture,
                out parsed))
            {
                return null;
            }

            return Math.Round(parsed, 2, MidpointRounding.AwayFromZero);
        }

        /// <summary>
        /// Maps Access Yes/No / SQL BIT to bool. Null/DBNull/false/0 → false; any other non-zero (incl. Access -1) → true.
        /// </summary>
        public static bool ToBool(object value)
        {
            if (value == null || value == DBNull.Value)
            {
                return false;
            }

            if (value is bool boolValue)
            {
                return boolValue;
            }

            if (value is byte || value is sbyte || value is short || value is ushort ||
                value is int || value is uint || value is long || value is ulong)
            {
                return Convert.ToInt64(value, CultureInfo.InvariantCulture) != 0;
            }

            if (value is float || value is double || value is decimal)
            {
                return Convert.ToDecimal(value, CultureInfo.InvariantCulture) != 0m;
            }

            var text = Convert.ToString(value, CultureInfo.InvariantCulture);
            if (string.IsNullOrWhiteSpace(text))
            {
                return false;
            }

            if (bool.TryParse(text, out var parsedBool))
            {
                return parsedBool;
            }

            long parsedLong;
            if (long.TryParse(text, NumberStyles.Any, CultureInfo.InvariantCulture, out parsedLong))
            {
                return parsedLong != 0;
            }

            return false;
        }
    }

    public sealed class ShopProductRow
    {
        /// <summary>SQL Express tblProduct.ProductID (same value as Access AutoNumber after Leg 1 sync).</summary>
        public int? SqlExpressProductId { get; set; }
        public string Code { get; set; }
        public string Description { get; set; }
        public string Category { get; set; }
        public string SubCategory { get; set; }
        public string Item { get; set; }
        public string Gender { get; set; }
        public string Era { get; set; }
        public string Barcode { get; set; }
        public decimal? Price { get; set; }
        public string Size { get; set; }
        public string Slug { get; set; }
        /// <summary>From tblProduct.blnCameraReady — source of truth for shop_products.is_visible.</summary>
        public bool IsVisible { get; set; }
        public string ImageUrl1 { get; set; }
        public string ImageUrl2 { get; set; }
        public string ImageUrl3 { get; set; }
        public string RowHash { get; set; }
    }
}