group SubscriptionSqlDao;

insertSubscription() ::= <<
    insert into subscriptions (
        id
      , bundle_id
      , category
      , start_date
      , bundle_start_date
      , active_version
      , charged_through_date
      , paid_through_date
      , created_by
      , created_date
      , updated_by
      , updated_date
    ) values (
        :id
      , :bundleId
      , :category
      , :startDate
      , :bundleStartDate
      , :activeVersion
      , :chargedThroughDate
      , :paidThroughDate
      , :userName
      , :createdDate
      , :userName
      , :updatedDate
    );
>>

getSubscriptionFromId() ::= <<
    select
        id
      , bundle_id
      , category
      , start_date
      , bundle_start_date
      , active_version
      , charged_through_date
      , paid_through_date    
    from subscriptions
    where id = :id
    ;
>>

getSubscriptionsFromBundleId() ::= <<
    select
      id
      , bundle_id
      , category
      , start_date
      , bundle_start_date
      , active_version
      , charged_through_date
      , paid_through_date    
    from subscriptions
    where bundle_id = :bundleId
    ;
>>

updateChargedThroughDate() ::= <<
    update subscriptions
    set
      charged_through_date = :chargedThroughDate
      , updated_by = :userName
      , updated_date = :updatedDate
    where id = :id
    ;
>>

updateActiveVersion() ::= <<
    update subscriptions
    set
      active_version = :activeVersion
      , updated_by = :userName
      , updated_date = :updatedDate
    where id = :id
    ;
>>

updateForRepair() ::= <<
    update subscriptions
    set
      active_version = :activeVersion
      , start_date = :startDate
      , bundle_start_date = :bundleStartDate
      , updated_by = :userName
      , updated_date = :updatedDate
    where id = :id
    ;
>>

getRecordId() ::= <<
    SELECT record_id
    FROM subscriptions
    WHERE id = :id;
>>

auditFields(prefix) ::= <<
    <prefix>table_name,
    <prefix>record_id,
    <prefix>change_type,
    <prefix>change_date,
    <prefix>changed_by,
    <prefix>reason_code,
    <prefix>comments,
    <prefix>user_token
>>

insertAuditFromTransaction() ::= <<
    INSERT INTO audit_log(<auditFields()>)
    VALUES(:tableName, :recordId, :changeType, :createdDate, :userName, NULL, NULL, NULL);
>>
