DB schema for retail platform BI integrations

Documentation for core database schema that is exposed via for BI integration.

BI schema has the following data tables:

Table	Description
`slip`	List of betting slips sold
`bet`	List of bets (single slip could have multiple bets)
`bet_leg`	List of bet legs, multi-leg accumulator have more than one leg per bet
`shop`	List of betting shops
`pos`	List of POS, each POS belongs to one shop
`shift`	List of shifts, each shift is associated with one cashier and POS
`cash_op`	List of cash operations between punters and POS (cashier)
`float_op`	Internal cash operations between operator accounts and POS
`cashier`	List of cashier accounts (that have access to cashier.adv.bet)
`admin`	List of management accounts (that have access to admin.adv.bet)

Field	Description
`slip_id`	Numeric Slip ID, unique, printed on each betting slip.
`shop_id`	Shop where slip was sold. Reference to `shop` table.
`shop_name`	Name of the shop where this slip was issued.
`shift_id`	Shift on which slip was sold. Reference to `shift` table.
`pay_out_shift_id`	Shift on which slip was paid out. Null for losing or not paid out slips. Reference to `shift` table.
`pay_out_shop_id`	Shop where slip was paid out. Null for losing or not paid out slips. Reference to `shop` table.
`currency_code`	Currency code for slip cash operations.
`state`	Slip state. Enum field. `n` - new betting slip `w` - winner not paid-out `W` - winner paid-out `L` - loser `r` - revoked not paid-out `R` - revoked paid-out `K` - expired or forced loser slip
`printed`	True if slip is reported to be printed successfully.
`revoked`	True if slip is marked as revoked.
`issue_time`	Time when slip was purchased/issued (main index for time based filtering)
`unrevokable_time`	Time after which punter can no longer revoke the slip (if revocation is allowed at all).
`resolve_time`	Time when slip was initially resulted (slip might be re-resulted later of event result changes).
`pay_out_time`	Time when slip was paid-out.
`expire_time`	Time when non-paid out slip will be converted to expired state (no longer pay-outable).
`stake_total`	Total stakes in the slip.
`win_total`	Total amount of money won by this slip.
`paid_in`	Amount of money paid-in by the punter for the slip (always equal to `price_total`).
`paid_out`	Amount of money paid-out to the punter (usually equal to 'win_total_d').

Field	Description
`slip_id`	Reference to `slip` table
`issue_time`	Time when slip with this bet was purchased/issued (main index for time based filtering)
`bet_id`	Unique ID for a bet
`game_id`	Product ID (Aardvark internal)
`stake`	Stake for this bet
`odds`	Current odds for this bet (might not match initial odds, for example in dead-heat situations, keno game)
`issued_odds`	Initial odds when bet was placed
`bonus_multiplier`	Multi-leg accumulator bonus multiplier
`state`	Bet state 1 - active 2 - won 3 - lost 4 - cancelled
`estimated_win`	Estimated value of this slip
`active_legs`	Number of legs not yet resolved
`total_legs`	Number of legs in this bet

Field	Description
`slip_id`	Reference to `slip` table
`issue_time`	Time when slip with this bet leg was purchased/issued (main index for time based filtering)
`bet_id`	Reference to `bet` table
`game_id`	Product ID (Aardvark internal)
`event_uuid`	Reference to event
`event_title`	Brief event description (teams names for sport events)
`event_sport`	Event sport type
`bet_leg_id`	Unique ID for this row
`market`	Bet market (type of bet placed)
`selection`	Bet selection (chosen market outcome)
`data`	Extra bet related data (Aardvark internal)
`odds`	Actual odds for this selection
`issued_odds`	Original odds when bet was issued
`state`	Selection state 1 - active 2 - won 3 - lost 4 - cancelled

Single entry in this table represents one physical retail betting shop.

Field	Description
`shop_id`	Unique shop ID
`display_name`	Shop name as configured in the admin interface
`currency_code`	ISO-4217 currency code for cash operations used in this shop
`location`	Shop address
`disabled`	Set to `1` (true) if this shop is disables and no operations in this shop is permitted, or `0` (false) otherwise

Single entry in this table represents one physical POS (Point of Sales) or till in a betting shop.

Field	Description
`pos_id`	Unique POS ID
`shop_id`	Reference to `shop` table
`shop_name`	Name of the shop where this POS is located.
`active_shift_id`	Reference to `shift` table if this till has an active shift
`display_name`	Name or identified of this till as configured via admin interface
`machine_uuid`	A restriction for computer identifier that is allowed to start new shift in this till
`disabled`	Set to `1` (true) if this shop is disables and no operations in this shop is permitted, or `0` (false) otherwise

Shift is a cashier work session. Single entry in this table shows the summary of a single shift.

Field	Description
`shift_id`	Unique shift ID
`user_id`	Reference to `cashier` table
`user_login_name`	User name of cashier working on this shift
`user_real_name`	Full name of cashier working on this shift
`pos_id`	Reference to `pos` table
`shop_id`	Reference to `shop` table
`currency_code`	ISO-4217 currency code of money values in this shift
`paid_in_count`	Total number of cash pay-in operations (same as total number of slips sold)
`paid_out_count`	Total number of cash pay-out operations (same as number of winning slip payouts plus revoked slip payouts)
`negative_balance`	Set to `1` (true) if this shift is allowed to operate with negative till balance, or `0` (false) otherwise
`paid_in_amount`	Total amount of cash collected from punters in this shift
`paid_out_amount`	Total amount of cash paid out to punters on this shift
`float_in_count`	Total number of float pay-in operations (if float operations are used)
`float_out_count`	Total number of float pay-out operations (if float operations are used)
`float_in_amount`	Total amount of cash taken from the shop vault to the till
`float_out_amount`	Total amount of cash taken from the till to the shop vault
`start_time`	Time in UTC when shift was started (main index for time based filtering)
`end_time`	Time in UTC when shift was closed or zero value if shift is still open
`expire_time`	Time in UTC when shift would be automatically closed
`machine_uuid`	Unique identifier of the computer used by the cashier for this shift
`locked_out`	Set to `1` (true) if shift is locked (via the management interface or because critical till balance is reached), or `0` (false) otherwise

Single entry in this table describes a single cash operation between a punter and a cashier.

Field	Description
`cash_op_uuid`	Unique cash operation ID
`shift_id`	Reference to `shift` table
`user_id`	Reference to `cashier` table, unique ID of cashier that performed this cash operation
`user_login_name`	User name of cashier account that performed this cash operation
`user_real_name`	Full name of cashier that performed this cash operation
`shop_id`	Reference to `shop` table, unique shop ID where this cash operation was performed
`shop_name`	Shop name where this cash operation was performed
`slip_id`	Reference to `slip` table
`slip_uuid`	Reference to `slip` table
`amount`	Amount of money transferred
`currency_code`	ISO-4217 currency code of money values in `amount_d` field
`tstamp`	Time in UTC when the cash operation took place (main index for time based filtering)
`slip_state`	Type of cash operation. Enum field. `n` - pay-in for new slip `W` - payout for winning slip `R` - pay-out for revoked slip

Single entry in this table describes a single cash operation between a shop vault and a till.

Field	Description
`float_op_uuid`	Unique float operation ID
`shift_id`	Reference to `shift` table
`user_id`	Reference to `admin` table or `cashier` table, account that performed float operation
`user_login_name`	User name of cashier or admin account that performed this cash operation
`user_real_name`	Full name of cashier or admin that performed this cash operation
`shop_id`	Reference to `shop` table, unique ID of shop where this float operation was performed
`shop_name`	Shop name where this float operation was performed
`slip_uuid`	Reference to `slip` table
`amount`	Amount of money transferred
`tstamp`	Time in UTC when the cash operation took place (main index for time based filtering)

Single entry in this table describes a single cashier account.

Field	Description
`user_id`	Unique account ID
`username`	Name used to sign-in to the system
`display_name`	Actual first and last name of the cashier
`active_shift_id`	Reference to `shift` table if this cashier has an open shift
`disabled`	Set to `1` if account is disabled, or `0` otherwise

Single entry in this table describes a single administrator account.

Field	Description
`user_id`	Unique account ID
`username`	Name used to sign-in to the system
`display_name`	Actual first and last name of the owner of this account
`disabled`	Set to `1` if account is disabled, or `0` otherwise