namespace EntitlementDAL.QueryBuilders;

// Required indexes:
//   MENTITLEMENTBUNDLE: IX (CLIENTID, ISCURRENT)
public static class BundleQB
{
    private const string COLUMNS = @"
       BUNDLEID     AS BundleId,
       CLIENTID     AS ClientId,
       BUNDLEJSON   AS BundleJson,
       SIGNATURE    AS Signature,
       KEYID        AS KeyId,
       ISCURRENT    AS IsCurrent,
       ISSUEDON     AS IssuedOn,
       VALIDUNTIL   AS ValidUntil,
       VERSION      AS Version,
       STATUS       AS Status,
       SORTORDER    AS SortOrder,
       CREATEDBYID  AS CreatedById,
       CREATEDON    AS CreatedOn,
       MODIFIEDBYID AS ModifiedById,
       MODIFIEDON   AS ModifiedOn,
       SOURCETYPE   AS SourceType,
       TENANTID     AS TenantId";

    public const string GET_CURRENT_BY_CLIENT = "SELECT" + COLUMNS + @"
FROM   MENTITLEMENTBUNDLE
WHERE  CLIENTID = @ClientId
  AND  ISCURRENT = 1";

    public const string GET_BY_ID = "SELECT" + COLUMNS + @"
FROM   MENTITLEMENTBUNDLE
WHERE  BUNDLEID = @BundleId";

    public const string EXPIRE_PRIOR_CURRENT = @"
UPDATE MENTITLEMENTBUNDLE SET
    ISCURRENT    = 0,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE  CLIENTID = @ClientId
  AND  ISCURRENT = 1";

    public const string INSERT = @"
INSERT INTO MENTITLEMENTBUNDLE
    (BUNDLEID, CLIENTID, BUNDLEJSON, SIGNATURE, KEYID, ISCURRENT, ISSUEDON, VALIDUNTIL,
     VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
VALUES
    (@BundleId, @ClientId, @BundleJson, @Signature, @KeyId, @IsCurrent, @IssuedOn, @ValidUntil,
     @Version, @Status, @SortOrder, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SourceType, @TenantId)";

    // Bundles expiring within N days — used by EntitlementBundleRefreshJob.
    public const string GET_EXPIRING_SOON = "SELECT" + COLUMNS + @"
FROM   MENTITLEMENTBUNDLE
WHERE  ISCURRENT  = 1
  AND  VALIDUNTIL <= @CutoffDate";
}