@startuml
'Import ERD style
!include ../atom_files/styles/erd.puml

'diagram title'
title Database changes for Mimos 2.0

'a brief description of the diagram'
legend top
    -- \t**Abstract:**\t --
    This ERD comprises all databases changes required to support the new Mimos
    journey.
    Business requirements includes:
    # ""(new)"" Cards for Offers
    # ""(new)"" Hub offer rating
    # ""(new)"" Favorite Hub Offers
    # ""(new)"" Visualization controls - Offer rating and Cards
    # ""(update)"" Refined expiration controls for Hub and Eligibility offers
    # ""(update)"" Tutorial for Mimos
    -- <size: 20><b>Legend</b></size> --
    Primary Key = primary_key(BOLD)
    Foreign Key = foreign_key(ITALICS)
    Not Null = not_null(UNDERSCORED)
    Unique = unique(GREEN)
    New/Changed = new_column(BLUE)
end legend

'entities'
note left of mimo_card
    **MIMO_CARD**
    Stores all the information related to a Mimo Card.
    This card is created by System Management and will
    be retrieved by the App to be shown to the Customers
    when they enter the new Mimo's section.
    Each "Card" is related to whether a Hub Offer or
    Eligibility Offer (one at a time), and each Offer
    may have only one Mimo Card associated with it.
    The column ""RELATED_OFFER_TYPE"" dictates to
    which kind of offer it is realted. If it is of
    ""HUB"" type then the column ""HUB_OFFER_UID""
    will contain the value of the offer to which it
    is related. Likewise, if it is of the type
    ""ELIGIBILITY"", then the column ""OFFER_UID""
    will contain the value of the offer to which it
    is related to.
end note
table(mimo_card, "MIMO_CARD") {
    'table primary key'
    {field} primary_key(MIMO_CARD_UID)
    --
    'table columns'
    {field} not_null(VERSION)
    {field} not_null(RELATED_OFFER_TYPE) (HUB, ELIGIBILITY)
    {field} unique(foreign_key(HUB_OFFER_UID))
    {field} unique(foreign_key(OFFER_UID))
    {field} not_null(HEADER_TITLE)
    {field} not_null(HEADER_DESCRIPTION)
    {field} not_null(FOOTER_TITLE)
    {field} not_null(FOOTER_DESCRIPTION)
    {field} START_DATETIME
    {field} END_DATETIME
    {field} not_null(LOGO)
    {field} not_null(STATUS) (ACTIVE, INACTIVE)
    {field} not_null(SORT_ORDER)
    {field} not_null(DELETE_FLG)
    --
    {field} not_null(CREATE_DT)
    {field} not_null(CREATE_USER_UID)
    {field} not_null(CREATE_FUNCTION_NAME)
    {field} not_null(UPDATE_DT)
    {field} not_null(UPDATE_USER_UID)
    {field} not_null(UPDATE_FUNCTION_NAME)
}

note right of mimo_card_visualization
    **MIMO_CARD_VISUALIZATION**
    Each MIMO_CARD created will be seen by each customer
    only once. So in order to track when a Customer has
    seen a card, the App will tell Core Next when it happens
    and Core Next will store the details of the visualization
    in this table.
    When retrieving a new MIMO_CARD to be shown to the
    Customer, Core Next will first check here which cards
    were already seen and filter them out of the possible cards
    to be shown.
end note
table(mimo_card_visualization, "MIMO_CARD_VISUALIZATION") {
    'table primary key'
    {field} primary_key(MIMO_CARD_VISUALIZATION_UID)
    --
    {field} foreign_key(USER_UID)
    {field} foreign_key(MIMO_CARD_UID)
    --
    {field} not_null(CREATE_DT)
    {field} not_null(CREATE_USER_UID)
    {field} not_null(CREATE_FUNCTION_NAME)
    {field} not_null(UPDATE_DT)
    {field} not_null(UPDATE_USER_UID)
    {field} not_null(UPDATE_FUNCTION_NAME)
}


note right of offer_rating_refusal
    **OFFER_RATING_REFUSAL**
    When a Customer refuses to rate a Offer
    (Hub or Eligibility), Core Next will
    store his decision here.
    This decision will be used to determine
    whether the App should ask the Customer
    to rate an Offer or not.

end note
table(offer_rating_refusal, "OFFER_RATING_REFUSAL") {
    'table primary key'
    {field} primary_key(OFFER_RATING_VISUALIZATION_UID)
    --
    {field} foreign_key(USER_UID)
    {field} foreign_key(ALLOCATED_VOUCHER_UID)
    {field} foreign_key(HUB_OFFER_USAGE_UID)
    {field} not_null(REFUSAL_DT)
    --
    {field} not_null(CREATE_DT)
    {field} not_null(CREATE_USER_UID)
    {field} not_null(CREATE_FUNCTION_NAME)
    {field} not_null(UPDATE_DT)
    {field} not_null(UPDATE_USER_UID)
    {field} not_null(UPDATE_FUNCTION_NAME)
}

note left of hub_offer_rating
    **HUB_OFFER_RATING**
    Has all the ratings (score and feedback message)
    for Hub Offers.
    It is already possible to rate Eligibility Offers,
    but now it will also be possible to rate Hub Offers.

    The rating is associated with the Hub Offer, but it
    may change (see Clarification below).

    **CLARIFICATION**
    Will we have to track the usage of Hub Offers?
    It would be necessary to do so if we have to ask
    the customer to rate a Hub Offer after 5 days he's
    used it. (EF item 7).
    If we'll keep track of Hub Offer usage, then the
    rating of a Hub Offer should be associated with
    the offer itself or with the usage (like eligiility
    offers)?

    'Considering that Hub Offers does not have the
    'concept of vouchers, the rating will be associated
    'with the Hub Offer itself, instead of associating
    'it with the usage (HUB_OFFER_USAGE)
end note
table(hub_offer_rating, "HUB_OFFER_RATING") {
    'table primary key'
    {field} primary_key(HUB_OFFER_RATING_UID)
    --
    {field} foreign_key(HUB_OFFER_UID)
    {field} foreign_key(USER_UID)
    {field} not_null(SCORE)
    {field} FEEDBACK_MSG
    {field} not_null(FEEDBACK_DT)
    --
    {field} not_null(CREATE_DT)
    {field} not_null(CREATE_USER_UID)
    {field} not_null(CREATE_FUNCTION_NAME)
    {field} not_null(UPDATE_DT)
    {field} not_null(UPDATE_USER_UID)
    {field} not_null(UPDATE_FUNCTION_NAME)

}

note right of hub_offer_favorite
    **HUB_OFFER_FAVORITE**
    This table will store all the favorite
    Hub Offers of each customer.
    A favorite Hub Offer is defined by the
    Customer when he clicks on the "Star"
    on a Offer.
end note
table(hub_offer_favorite, "HUB_OFFER_FAVORITE") {
    'table primary key'
    {field} primary_key(FAVORITE_HUB_OFFER_UID)
    --
    {field} foreign_key(HUB_OFFER_UID)
    {field} foreign_key(USER_UID)
    --
    {field} not_null(CREATE_DT)
    {field} not_null(CREATE_USER_UID)
    {field} not_null(CREATE_FUNCTION_NAME)
    {field} not_null(UPDATE_DT)
    {field} not_null(UPDATE_USER_UID)
    {field} not_null(UPDATE_FUNCTION_NAME)
}

note left of hub_offer_usage
    **HUB_OFFER_USAGE**
    Every use of a Hub Offer by a Customer will
    be recorded in ""HUB_OFFER_USAGE""
end note
table(hub_offer_usage, "HUB_OFFER_USAGE") {
    'table primary key'
    {field} primary_key(HUB_OFFER_USAGE_UID)
    --
    {field} foreign_key(HUB_OFFER_UID)
    {field} foreign_key(USER_UID)
    {field} not_null(USAGE_DT)
    --
    {field} not_null(CREATE_DT)
    {field} not_null(CREATE_USER_UID)
    {field} not_null(CREATE_FUNCTION_NAME)
    {field} not_null(UPDATE_DT)
    {field} not_null(UPDATE_USER_UID)
    {field} not_null(UPDATE_FUNCTION_NAME)

}

table(users, "USERS") #ffeaa8 {
    {field} primary_key(USER_UID)
}

table(offer, "OFFER") #ffeaa8 {
    {field} primary_key(OFFER_UID)
    {field} new_column(BENEFIT_DESCRIPTION)
    {field} new_column(LIST_IMAGE)
}

table(hub_offer, "HUB_OFFER") #ffeaa8 {
    {field} primary_key(HUB_OFFER_UID)
    {field} new_column(START_DATE)
}

table(voucher, "VOUCHER") #ffeaa8 {
    {field} primary_key(VOUCHER_UID)
    {field} foreign_key(OFFER_UID)
}

table(allocated_voucher, "ALLOCATED_VOUCHER") #ffeaa8 {
    {field} primary_key(ALLOCATED_VOUCHER_UID)
    {field} foreign_key(VOUCHER_UID)
    {field} foreign_key(USER_UID)
}

''offer_rating_viewed_control and mimo_card_visualization -> better way of doing it??
'entity relationships'

allocated_voucher -down-{  offer_rating_refusal
hub_offer_usage -right-{  offer_rating_refusal
offer_rating_refusal -[hidden]up- allocated_voucher
offer -[hidden]left- users
offer -up-{ mimo_card
offer -right-{ voucher
voucher -down-{ allocated_voucher
users -right-{ allocated_voucher
users -down-{ hub_offer_usage
users -down-{ offer_rating_refusal
mimo_card -right-{ mimo_card_visualization
users -up-{ mimo_card_visualization
users -down-{ hub_offer_rating
users -down-{ hub_offer_favorite
hub_offer -left-{ hub_offer_rating
hub_offer -right-{ hub_offer_favorite
hub_offer -left-{ mimo_card
hub_offer -down-{ hub_offer_usage

@enduml
