using Microsoft.Data.Sqlite;
using System.Data;
using Dapper;

namespace BugReport
{
    public class DateTimeToTimestampHandler : SqlMapper.TypeHandler<DateTimeOffset?>
    {
        public override void SetValue(IDbDataParameter parameter, DateTimeOffset? value)
        {
            Console.WriteLine($"SetValue was hit, input value is {value.Value}");
            parameter.Value = value.Value.ToUnixTimeSeconds();
        }
        public override DateTimeOffset? Parse(object value)
        {
            Console.WriteLine($"Parse was hit, input value is {value} - converted to {DateTimeOffset.FromUnixTimeSeconds((long)value)}");
            return DateTimeOffset.FromUnixTimeSeconds((long)value);
        }
    }

    public class BugReport
    {
        public static void Main()
        {
            DateTimeOffset? myTimestamp = DateTimeOffset.UtcNow;
            Dapper.SqlMapper.AddTypeHandler(new DateTimeToTimestampHandler());

            var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            SQLitePCL.raw.sqlite3_trace(connection.Handle, (_, statement) => Console.WriteLine($"Sent to SQLite: {statement}"), null);

            Console.WriteLine("SQLite version is " + connection.ExecuteScalar("SELECT sqlite_version()"));
            connection.Execute("CREATE TABLE BugReport (ThisIsAnIntColumn INTEGER) STRICT");
            connection.Execute("INSERT INTO BugReport Values (1653915600)");
            var firstSelect = connection.Query<DateTimeOffset?>("SELECT * FROM BugReport");
            Console.WriteLine($"Mapped result is {firstSelect.First()}");
            try
            {
                connection.Execute("INSERT INTO BugReport VALUES (@MyTimestamp)", new { MyTimestamp = myTimestamp });
            }

            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

        }
    }
}