using System;
using System.Data;
using Dapper;

namespace GB5Shared.QueryExecutor
{
    /// <summary>
    /// Dapper type handlers for modern .NET date types.
    ///
    /// SQL Server stores dates as datetime/datetime2 (DateTime).
    /// These handlers bridge the gap so DAL code can use DateOnly and
    /// DateTimeOffset directly in DTO properties without manual conversion.
    ///
    /// Registration: call GB5DapperTypeHandlers.Register() once at startup
    /// (QueryExecutor static initializer calls this automatically).
    /// </summary>
    public static class GB5DapperTypeHandlers
    {
        private static bool _registered;
        private static readonly object _lock = new();

        public static void Register()
        {
            if (_registered) return;
            lock (_lock)
            {
                if (_registered) return;

                SqlMapper.AddTypeHandler(new DateOnlyTypeHandler());
                SqlMapper.AddTypeHandler(new NullableDateOnlyTypeHandler());
                SqlMapper.AddTypeHandler(new DateTimeOffsetFromDateTimeHandler());
                SqlMapper.AddTypeHandler(new NullableDateTimeOffsetFromDateTimeHandler());

                _registered = true;
            }
        }

        // SQL Server datetime minimum — 0001-01-01 is below it; send DBNull instead.
        private static readonly DateTime _sqlMinDate =
            new DateTime(1753, 1, 1, 0, 0, 0, DateTimeKind.Utc);

        // ── DateOnly ────────────────────────────────────────────────────────────

        private sealed class DateOnlyTypeHandler : SqlMapper.TypeHandler<DateOnly>
        {
            public override void SetValue(IDbDataParameter parameter, DateOnly value)
            {
                if (value == DateOnly.MinValue)
                {
                    parameter.Value = DBNull.Value;
                    return;
                }

                parameter.DbType = DbType.Date;
                parameter.Value = value.ToDateTime(TimeOnly.MinValue);
            }

            public override DateOnly Parse(object value) =>
                value is DateTime dt
                    ? DateOnly.FromDateTime(dt)
                    : DateOnly.Parse(value.ToString()!);
        }

        private sealed class NullableDateOnlyTypeHandler : SqlMapper.TypeHandler<DateOnly?>
        {
            public override void SetValue(IDbDataParameter parameter, DateOnly? value)
            {
                if (value is null)
                {
                    parameter.Value = DBNull.Value;
                    return;
                }

                parameter.DbType = DbType.Date;
                parameter.Value = value.Value.ToDateTime(TimeOnly.MinValue);
            }

            public override DateOnly? Parse(object value)
            {
                if (value is DBNull || value is null) return null;
                return value is DateTime dt
                    ? DateOnly.FromDateTime(dt)
                    : DateOnly.Parse(value.ToString()!);
            }
        }

        // ── DateTimeOffset ───────────────────────────────────────────────────────
        // SQL columns are datetime/datetime2 (no tz). We store UTC and reconstruct
        // a UTC DateTimeOffset on read.

        private sealed class DateTimeOffsetFromDateTimeHandler : SqlMapper.TypeHandler<DateTimeOffset>
        {
            public override void SetValue(IDbDataParameter parameter, DateTimeOffset value)
            {
                var utc = value.UtcDateTime;
                if (value == DateTimeOffset.MinValue || utc < _sqlMinDate)
                {
                    parameter.Value = DBNull.Value;
                    return;
                }

                parameter.DbType = DbType.DateTime2;
                parameter.Value = utc;
            }

            public override DateTimeOffset Parse(object value)
            {
                if (value is DateTimeOffset dto) return dto;
                if (value is DateTime dt)
                    return new DateTimeOffset(
                        dt.Kind == DateTimeKind.Unspecified
                            ? DateTime.SpecifyKind(dt, DateTimeKind.Utc)
                            : dt);
                return DateTimeOffset.Parse(value.ToString()!);
            }
        }

        private sealed class NullableDateTimeOffsetFromDateTimeHandler : SqlMapper.TypeHandler<DateTimeOffset?>
        {
            public override void SetValue(IDbDataParameter parameter, DateTimeOffset? value)
            {
                if (value is null)
                {
                    parameter.Value = DBNull.Value;
                    return;
                }

                var utc = value.Value.UtcDateTime;
                if (value.Value == DateTimeOffset.MinValue || utc < _sqlMinDate)
                {
                    parameter.Value = DBNull.Value;
                    return;
                }

                parameter.DbType = DbType.DateTime2;
                parameter.Value = utc;
            }

            public override DateTimeOffset? Parse(object value)
            {
                if (value is DBNull || value is null) return null;
                if (value is DateTimeOffset dto) return dto;
                if (value is DateTime dt)
                    return new DateTimeOffset(
                        dt.Kind == DateTimeKind.Unspecified
                            ? DateTime.SpecifyKind(dt, DateTimeKind.Utc)
                            : dt);
                return DateTimeOffset.Parse(value.ToString()!);
            }
        }
    }
}