using System;
using System.Data;
using System.Globalization;
using ServiceStack.OrmLite.Converters;
using ServiceStack.Text.Common;

namespace ServiceStack.OrmLite.Sqlite.Converters
{
    public class SqliteNativeDateTimeConverter : DateTimeConverter
    {
        public override string ColumnDefinition => "VARCHAR(8000)";

        public override DbType DbType => DbType.DateTime;

        public override string ToQuotedString(Type fieldType, object value)
        {
            var dateTime = (DateTime)value;
            if (DateStyle == DateTimeKind.Unspecified)
            {
                dateTime = DateTime.SpecifyKind(dateTime, DateTimeKind.Local);
            }
            else if (DateStyle == DateTimeKind.Local && dateTime.Kind == DateTimeKind.Unspecified)
            {
                dateTime = DateTime.SpecifyKind(dateTime, DateTimeKind.Utc).ToLocalTime();
            }
            else if (DateStyle == DateTimeKind.Utc)
            {
                dateTime = dateTime.Kind == DateTimeKind.Local
                    ? DateTime.SpecifyKind(dateTime, DateTimeKind.Local).ToUniversalTime()
                    : DateTime.SpecifyKind(dateTime, DateTimeKind.Utc);

                return DialectProvider.GetQuotedValue(dateTime.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), typeof(string));
            }

            var dateStr = DateTimeSerializer.ToLocalXsdDateTimeString(dateTime);
            dateStr = dateStr.Replace("T", " ");
            const int tzPos = 6; //"-00:00".Length;
            var timeZoneMod = dateStr.Substring(dateStr.Length - tzPos, 1);
            if (timeZoneMod == "+" || timeZoneMod == "-")
            {
                dateStr = dateStr.Substring(0, dateStr.Length - tzPos);
            }

            return DialectProvider.GetQuotedValue(dateStr, typeof(string));
        }

        public override object FromDbValue(Type fieldType, object value)
        {
            var dateTime = (DateTime)value;

            if (DateStyle == DateTimeKind.Unspecified)
                dateTime = DateTime.SpecifyKind(dateTime, DateTimeKind.Local);

            return base.FromDbValue(dateTime);
        }

        public override object GetValue(IDataReader reader, int columnIndex, object[] values)
        {
            try
            {
                if (values != null && values[columnIndex] == DBNull.Value)
                    return null;

                if (reader.IsDBNull(columnIndex))
                    return null;

                return reader.GetDateTime(columnIndex);
            }
            catch (Exception ex)
            {
                var value = base.GetValue(reader, columnIndex, values);
                if (value == null)
                    return null;

                if (!(value is string dateStr))
                    throw new Exception($"Converting from {value.GetType().Name} to DateTime is not supported");

                Log.Warn("Error reading string as DateTime in Sqlite: " + dateStr, ex);
                return DateTime.Parse(dateStr);
            }
        }
    }

    /// <summary>
    /// New behavior from using System.Data.SQLite.Core
    /// </summary>
    public class SqliteCoreDateTimeConverter : SqliteNativeDateTimeConverter
    {
        public override object ToDbValue(Type fieldType, object value)
        {
            var dateTime = (DateTime)value;
            if (DateStyle == DateTimeKind.Utc)
            {
                if (dateTime.Kind == DateTimeKind.Local)
                {
                    dateTime = dateTime.ToUniversalTime();
                }
                else if (dateTime.Kind == DateTimeKind.Unspecified)
                {
                    dateTime = DateTime.SpecifyKind(dateTime, DateTimeKind.Utc);
                }
            }
            else if (DateStyle == DateTimeKind.Local && dateTime.Kind != DateTimeKind.Local)
            {
                dateTime = dateTime.Kind == DateTimeKind.Utc
                    ? dateTime.ToLocalTime()
                    : DateTime.SpecifyKind(dateTime, DateTimeKind.Utc).ToLocalTime();
            }
            else if (DateStyle == DateTimeKind.Unspecified && dateTime.Kind == DateTimeKind.Utc)
            {
                dateTime = DateTime.SpecifyKind(dateTime, DateTimeKind.Unspecified);
            }

            return dateTime;
        }

        public override object FromDbValue(Type fieldType, object value)
        {
            var dateTime = (DateTime)value;

            if (DateStyle == DateTimeKind.Utc)
            {
#if NETCORE
                //.NET Core returns correct Local time but as Unspecified so change to Local and Convert to UTC
                dateTime = DateTime.SpecifyKind(dateTime, DateTimeKind.Local).ToUniversalTime();
#else
                dateTime = dateTime.ToUniversalTime();
#endif
            }

            if (DateStyle == DateTimeKind.Local && dateTime.Kind != DateTimeKind.Local)
            {
                dateTime = dateTime.Kind == DateTimeKind.Utc
                    ? dateTime.ToLocalTime()
                    : DateTime.SpecifyKind(dateTime, DateTimeKind.Local);
            }

            return dateTime;
        }

        public override object GetValue(IDataReader reader, int columnIndex, object[] values)
        {
            var ret = base.GetValue(reader, columnIndex, values);
            return ret;
        }
    }

    public class SqliteDataDateTimeConverter : SqliteCoreDateTimeConverter
    {
        public override object FromDbValue(Type fieldType, object value)
        {
            var dateTime = (DateTime)value;

            if (DateStyle == DateTimeKind.Utc)
            {
                //.NET Core returns correct Local time but as Unspecified so change to Local and Convert to UTC
                dateTime = DateTime.SpecifyKind(dateTime, DateTimeKind.Utc); // don't convert
            }

            if (DateStyle == DateTimeKind.Local && dateTime.Kind != DateTimeKind.Local)
            {
                dateTime = dateTime.Kind == DateTimeKind.Utc
                    ? dateTime.ToLocalTime()
                    : DateTime.SpecifyKind(dateTime, DateTimeKind.Local);
            }

            return dateTime;
        }
    }

    public class SqliteWindowsDateTimeConverter : SqliteNativeDateTimeConverter
    {
        public override object FromDbValue(Type fieldType, object value)
        {
            var dateTime = (DateTime)value;

            if (DateStyle == DateTimeKind.Utc)
                dateTime = dateTime.ToUniversalTime();

            if (DateStyle == DateTimeKind.Local && dateTime.Kind != DateTimeKind.Local)
            {
                dateTime = dateTime.Kind == DateTimeKind.Utc
                    ? dateTime.ToLocalTime()
                    : DateTime.SpecifyKind(dateTime, DateTimeKind.Local);
            }

            return dateTime;
        }
    }
}