NotificationSqlDao.sql.stg

group NotificationSqlDao;

CHECK_TENANT() ::= "tenant_record_id = :tenantRecordId"
AND_CHECK_TENANT() ::= "AND <CHECK_TENANT()>"

getReadyNotifications() ::= <<
    select
      record_id
      , id
      , class_name
      , notification_key
      , user_token
      , future_user_token
      , created_date
      , creating_owner
      , effective_date
      , queue_name
      , processing_owner
      , processing_available_date
      , processing_state
      , account_record_id
      , tenant_record_id
    from notifications
    where
      effective_date \<= :now
      and processing_state != 'PROCESSED'
      and processing_state != 'REMOVED'
      and (processing_owner IS NULL OR processing_available_date \<= :now)
    order by
      effective_date asc
      , created_date asc
      , record_id
    limit :max
    ;
>>

getPendingCountNotifications() ::= <<
    select
      count(*)
    from notifications
    where
      effective_date \<= :now
      and processing_state = 'AVAILABLE'
    ;
>>

getFutureNotificationsForAccount() ::= <<
select
    record_id
  , id
  , class_name
  , notification_key
  , user_token
  , future_user_token
  , created_date
  , creating_owner
  , effective_date
  , queue_name
  , processing_owner
  , processing_available_date
  , processing_state
  , account_record_id
  , tenant_record_id
from notifications
where queue_name = :queueName
and processing_state = 'AVAILABLE'
and account_record_id = :accountRecordId
and effective_date >= :now
order by effective_date, record_id
;
>>

removeNotification()  ::= <<
  update notifications
    set
  processing_state = 'REMOVED'
    where
  id = :id
;
>>

claimNotification() ::= <<
    update notifications
    set
      processing_owner = :owner
      , processing_available_date = :nextAvailable
      , processing_state = 'IN_PROCESSING'
    where
      id = :id
      and processing_state != 'PROCESSED'
      and processing_state != 'REMOVED'
      and (processing_owner IS NULL OR processing_available_date \<= :now)
    ;
>>

clearNotification() ::= <<
    update notifications
    set
      processing_state = 'PROCESSED'
    where
      id = :id
    ;
>>

removeNotificationsByKey() ::= <<
    update notifications
    set
      processing_state = 'REMOVED'
    where
      notification_key = :notificationKey
    ;
>>

insertNotification() ::= <<
    insert into notifications (
      id
      , class_name
      , notification_key
      , user_token
      , future_user_token
      , created_date
      , creating_owner
      , effective_date
      , queue_name
      , processing_owner
      , processing_available_date
      , processing_state
      , account_record_id
      , tenant_record_id
    ) values (
      :id
      , :className
      , :notificationKey
      , :userToken
      , :futureUserToken
      , :createdDate
      , :creatingOwner
      , :effectiveDate
      , :queueName
      , :processingOwner
      , :processingAvailableDate
      , :processingState
      , :accountRecordId
      , :tenantRecordId
    );
>>

insertClaimedHistory() ::= <<
    insert into claimed_notifications (
          owner_id
        , claimed_date
        , notification_id
        , account_record_id
        , tenant_record_id
      ) values (
          :ownerId
        , :claimedDate
        , :notificationId
        , :accountRecordId
        , :tenantRecordId
      );
>>