NotificationSqlDao.sql.stg

group NotificationSqlDao;

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

getReadyNotifications() ::= <<
    select
      record_id
      , id
      , class_name
      , account_id
      , notification_key
      , created_date
      , creating_owner
      , effective_date
      , queue_name
      , processing_owner
      , processing_available_date
      , processing_state
      , account_record_id
      , tenant_record_id
    from notifications
    FORCE INDEX (idx_comp_where)
    where
      effective_date \<= :now
      and queue_name = :queueName
      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
    ;
>>

getNotificationForAccountAndDate() ::= <<
   select
     record_id
     , id
     , class_name
     , account_id
     , notification_key
     , created_date
     , creating_owner
     , effective_date
     , queue_name
     , processing_owner
     , processing_available_date
     , processing_state
     , account_record_id
     , tenant_record_id
   from notifications
   where
   account_id = :accountId AND effective_date = :effectiveDate
   ;
>>

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
      , account_id
      , notification_key
      , created_date
      , creating_owner
      , effective_date
      , queue_name
      , processing_owner
      , processing_available_date
      , processing_state
      , account_record_id
      , tenant_record_id
    ) values (
      :id
      , :className
      , :accountId
      , :notificationKey
      , :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
      );
>>