namespace PromotionExchangeDAL.Query;

public static class ExchangePackageQB
{
    public const string INSERT = @"
        INSERT INTO TEXCHANGEPACKAGE
            (ORIGINENVIRONMENTCODE, DESTINATIONENVIRONMENTCODE, PACKAGEBYTES, PACKAGESIZEBYTES, STATUS)
        VALUES
            (@OriginEnvironmentCode, @DestinationEnvironmentCode, @PackageBytes, @PackageSizeBytes, 0);
        SELECT SCOPE_IDENTITY();";

    // Index: IX_TEXCHANGEPACKAGE_DESTINATION_STATUS on (DestinationEnvironmentCode, Status).
    public const string GET_PENDING_FOR_DESTINATION = @"
        SELECT PACKAGEID             AS PackageId,
               ORIGINENVIRONMENTCODE AS OriginEnvironmentCode,
               PACKAGESIZEBYTES      AS PackageSizeBytes,
               UPLOADEDON            AS UploadedOn
        FROM   TEXCHANGEPACKAGE
        WHERE  DESTINATIONENVIRONMENTCODE = @DestinationEnvironmentCode
        AND    STATUS                     = 0
        ORDER BY UPLOADEDON";

    public const string GET_BY_ID_FOR_DESTINATION = @"
        SELECT PACKAGEID                   AS PackageId,
               ORIGINENVIRONMENTCODE       AS OriginEnvironmentCode,
               DESTINATIONENVIRONMENTCODE  AS DestinationEnvironmentCode,
               PACKAGEBYTES                AS PackageBytes,
               PACKAGESIZEBYTES            AS PackageSizeBytes,
               STATUS                      AS Status,
               UPLOADEDON                  AS UploadedOn,
               DOWNLOADEDON                AS DownloadedOn
        FROM   TEXCHANGEPACKAGE
        WHERE  PACKAGEID                  = @PackageId
        AND    DESTINATIONENVIRONMENTCODE = @DestinationEnvironmentCode";

    public const string MARK_DOWNLOADED = @"
        UPDATE TEXCHANGEPACKAGE
        SET    STATUS       = 1,
               DOWNLOADEDON = SYSUTCDATETIME()
        WHERE  PACKAGEID = @PackageId";
}