using System;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion;

namespace GB5Shared.Query.OutBox
{
    public static class OutBoxQB
    {
        // --------------------------------------------------
        // 1️⃣ INSERT INTO OUTBOX (used in PublishEventAsync)
        // --------------------------------------------------
        public const string InsertOutBox = @"
            INSERT INTO TOUTBOX
            (
                OBJECTTYPEID,
                OBJECTID,
                EVENTTYPEID,
                VERSION,
                CORRELATIONKEY,
                USERID,
                TENANTID,
                PAYLOAD,
                STATUS,
                CREATEDON,
                HEADERROWGUID,
                CONTEXTJSON
            )
            VALUES
            (
                @ObjectTypeId,
                @ObjectId,
                @EventTypeId,
                @Version,
                @CorrelationKey,
                @userid,
                @tenantid,
                @Payload,
                @Status,
                SYSUTCDATETIME(),
                @HeaderRowGuid,
                @ContextJson
            );
        ";
         
        public const string fetchSql = @"
                SELECT TOP (@BatchSize)
                       OUTBOXID,
                       OBJECTTYPEID,
                       OBJECTID,
                       EVENTTYPEID,
                       VERSION,
                       CORRELATIONKEY,
                       USERID,
                       TENANTID,
                       PAYLOAD,
                       STATUS,
                       CREATEDON,
                       HEADERROWGUID,
                       CONTEXTJSON
                FROM   TOUTBOX
                WHERE  STATUS = @Pending
                ORDER BY CREATEDON;
            ";
         
        public const string GetEventsByCorrelationKey = @"
            SELECT *
            FROM TOUTBOX
            WHERE CORRELATIONKEY = @CorrelationKey
            ORDER BY CREATEDON;
        ";

        public const string MarkPublished = @"
                        UPDATE TOUTBOX
                        SET STATUS = @Published
                        WHERE OUTBOXID = @OutboxId;";

        public const string MarkFailed = @"
                        UPDATE TOUTBOX
                        SET STATUS = @Failed
                        WHERE OUTBOXID = @OutboxId;";

        // Resets FAILED rows older than @MinutesOld back to PENDING so they are retried
        public const string ResetFailedToPending = @"
                        UPDATE TOUTBOX
                        SET STATUS = @Pending
                        WHERE STATUS = @Failed
                          AND CREATEDON <= DATEADD(MINUTE, -@MinutesOld, SYSUTCDATETIME());";
    }
}