with "terminal_locations" as (
    select
    p.public_id as terminal_public_id,
    p.id as terminal_id,
    l.id as location_id,
    case when array_agg(pg.id) && array[7, 11, 23, 26]::bigint[] then 'shop_terminal' else 'self_serviced' end as terminal_type,
    l."name" as location_name,
    l.street_address as address,
    l.postal_code as zip_code,
    l.city as city
    from "PrincipalMachine" as pm
    join "Principal" as p on pm.id = p.id
    join "PrincipalGroupMapping" as pgm on pgm.principal_id = p.id
    join "PrincipalGroup" as pg on pg.id = pgm.principal_group_id
    join "PlacementMachineLocationLatest" as pmll on pmll.machine_id = pm.id
    left outer join "PlacementMachineLocation" as pml on pml.machine_id = pm.id
    join "Location" as l on l.id = pml.location_id
    where pml.event_ts >= date_trunc('year', timestamp '2019-01-01') and pml.event_ts < date_trunc('year', timestamp '2020-01-01')
    group by p.public_id, p.id, l.id
),
"betting_sums" as (
select
    terminal_locations.terminal_public_id,
    terminal_locations.location_id,
    bet_sums.total_sales,
    (
        coalesce(settled_bets_sums.settled_bet_stake, 0) - coalesce(rollbacked_bets_sums.rollbacked_bet_stake, 0) + coalesce(cashed_out_bets_sums.cashed_out_bet_stake)
    )
    -
    (
        coalesce(settled_bets_sums.paid_out_amount, 0) + coalesce(rollbacked_bets_sums.rollback_amount, 0) + coalesce(cashed_out_bets_sums.cash_out_amount, 0)
    ) as total_bsi
    from terminal_locations
    full join (
        select
            terminal_locations.terminal_public_id,
            terminal_locations.location_id,
            count(distinct bs.id) as total_sales,
            sum(bb.stake) as total_bets_stake
        from terminal_locations
        right join "ActionContext" as ac on ac.location_id = terminal_locations.location_id
        join "ActionContextPrincipalMapping" as acpm on acpm.context_id = ac.id and acpm.principal_id = terminal_locations.terminal_id
        join "BettingSale" as bs on bs.context_id = ac.id
        right join "BettingBet" as bb on bb.sale_id = bs.id
        where bs.created_time >= '2019-01-01'::timestamptz and bs.created_time < '2020-01-01'::timestamptz
        group by
            terminal_locations.terminal_public_id,
            terminal_locations.location_id
    ) as bet_sums on bet_sums.terminal_public_id = terminal_locations.terminal_public_id and bet_sums.location_id = terminal_locations.location_id
    full join (
        select
            terminal_locations.terminal_public_id,
            terminal_locations.location_id,
            sum(bb.stake) as settled_bet_stake,
            sum(bbp.payout_amount) as paid_out_amount
        from terminal_locations
        right join "ActionContext" as ac on ac.location_id = terminal_locations.location_id
        join "ActionContextPrincipalMapping" as acpm on acpm.context_id = ac.id and acpm.principal_id = terminal_locations.terminal_id

        join "BettingSale" as bs on bs.context_id = ac.id
        right join "BettingBet" as bb on bb.sale_id = bs.id
        join "BettingBetPayout" as bbp on bbp.bet_id = bb.id
        where bs.created_time >= '2019-01-01'::timestamptz and bs.created_time < '2020-01-01'::timestamptz
        and bbp.cause = 'BET_SETTLEMENT'::"BettingBetPayoutCause"
        group by
            terminal_locations.terminal_public_id,
            terminal_locations.location_id
    ) as settled_bets_sums on settled_bets_sums.terminal_public_id = terminal_locations.terminal_public_id and settled_bets_sums.location_id = terminal_locations.location_id
    full join (
        select
            terminal_locations.terminal_public_id,
            terminal_locations.location_id,
            sum(bb.stake) as rollbacked_bet_stake,
            sum(bbp.payout_amount) as rollback_amount
        from terminal_locations
        right join "ActionContext" as ac on ac.location_id = terminal_locations.location_id
        join "ActionContextPrincipalMapping" as acpm on acpm.context_id = ac.id and acpm.principal_id = terminal_locations.terminal_id

        join "BettingSale" as bs on bs.context_id = ac.id
        right join "BettingBet" as bb on bb.sale_id = bs.id
        join "BettingBetPayout" as bbp on bbp.bet_id = bb.id
        where bs.created_time >= '2019-01-01'::timestamptz and bs.created_time < '2020-01-01'::timestamptz
        and bbp.cause = 'BET_SETTLEMENT_ROLLBACK'::"BettingBetPayoutCause"
        group by
            terminal_locations.terminal_public_id,
            terminal_locations.location_id
    ) as rollbacked_bets_sums on rollbacked_bets_sums.terminal_public_id = terminal_locations.terminal_public_id and rollbacked_bets_sums.location_id = terminal_locations.location_id
    full join (
        select
            terminal_locations.terminal_public_id,
            terminal_locations.location_id,
            sum(bb.stake) as cashed_out_bet_stake,
            sum(bbp.payout_amount) as cash_out_amount
        from terminal_locations
        right join "ActionContext" as ac on ac.location_id = terminal_locations.location_id
        join "ActionContextPrincipalMapping" as acpm on acpm.context_id = ac.id and acpm.principal_id = terminal_locations.terminal_id

        join "BettingSale" as bs on bs.context_id = ac.id
        right join "BettingBet" as bb on bb.sale_id = bs.id
        join "BettingBetPayout" as bbp on bbp.bet_id = bb.id
        where bs.created_time >= '2019-01-01'::timestamptz and bs.created_time < '2020-01-01'::timestamptz
        and bbp.cause = 'BET_CASH_OUT'::"BettingBetPayoutCause"
        group by
            terminal_locations.terminal_public_id,
            terminal_locations.location_id
    ) as cashed_out_bets_sums on cashed_out_bets_sums.terminal_public_id = terminal_locations.terminal_public_id and cashed_out_bets_sums.location_id = terminal_locations.location_id

),
"virtual_sums" as (
select
        terminal_locations.terminal_public_id,
        terminal_locations.location_id,
        virtual_sums.total_sales as total_sales,
        virtual_sums.virtual_bsi as total_bsi
    from terminal_locations
    full join (
        select
            terminal_locations.terminal_public_id,
            terminal_locations.location_id,
            (settled_virtual_bet_sums.bet_stake_sum - settled_virtual_bet_sums.bet_win_sum) as virtual_bsi,
            settled_virtual_bet_sums.sales_count as total_sales
        from terminal_locations
        full join (
            select
                terminal_locations.terminal_public_id,
                terminal_locations.location_id,
                sum(vbb.stake) as bet_stake_sum,
                sum(vbbr.actual_win) as bet_win_sum,
                count(distinct vbs.id) as sales_count
            from "VirtualBettingSale" as vbs
            join "ActionContext" as ac on ac.id = vbs.context_id
            join "ActionContextPrincipalMapping" as acpm on acpm.context_id = ac.id

            join terminal_locations on terminal_locations.location_id = ac.location_id
            right join "VirtualBettingBet" as vbb on vbb.sale_id = vbs.id
            join "VirtualBettingBetResult" as vbbr on vbbr.bet_id = vbb.id
            where vbs.created_time >= '2019-01-01'::timestamptz and vbs.created_time < '2020-01-01'::timestamptz
            and acpm.principal_id = terminal_locations.terminal_id
            group by
                terminal_locations.terminal_public_id,
                terminal_locations.location_id

        ) as settled_virtual_bet_sums on settled_virtual_bet_sums.location_id = terminal_locations.location_id and settled_virtual_bet_sums.terminal_public_id = terminal_locations.terminal_public_id
    ) as virtual_sums on virtual_sums.location_id = terminal_locations.location_id and virtual_sums.terminal_public_id = terminal_locations.terminal_public_id
),
"cash_sums" as (
select
        terminal_locations.terminal_public_id,
        terminal_locations.location_id,
        sales_exceeding_stake_limit.sales_count as deposit_limit_exceeded_count,
        cash_withdrawals_exceeding_limit.withdraws_count as withdraw_limit_exceeded_count
    from terminal_locations
    full join (
        select
            terminal_locations.terminal_public_id,
            terminal_locations.location_id,
            count(1) as sales_count
        from terminal_locations
        right join "ActionContext" as ac on ac.location_id = terminal_locations.location_id
        join "ActionContextPrincipalMapping" as acpm on acpm.context_id = ac.id and acpm.principal_id = terminal_locations.terminal_id

        join "BettingSale" as bs on bs.context_id = ac.id
        where bs.amount > (2000 * 7.5)
        group by
            terminal_locations.terminal_public_id,
            terminal_locations.location_id
    ) as sales_exceeding_stake_limit on sales_exceeding_stake_limit.terminal_public_id = terminal_locations.terminal_public_id and sales_exceeding_stake_limit.location_id = terminal_locations.location_id
    full join (
        select
            terminal_locations.terminal_public_id,
            terminal_locations.location_id,
            count(1) as withdraws_count
        from terminal_locations
        right join "WalletCashRegisterTransaction" as wcrt on wcrt.location_id = terminal_locations.location_id and wcrt.principal_machine_id = terminal_locations.terminal_id
        where wcrt.amount < -(2000 * 7.5)
        group by
            terminal_locations.terminal_public_id,
            terminal_locations.location_id
    ) as cash_withdrawals_exceeding_limit on cash_withdrawals_exceeding_limit.terminal_public_id = terminal_locations.terminal_public_id and cash_withdrawals_exceeding_limit.location_id = terminal_locations.location_id
)
select
terminal_locations.*,
coalesce(betting_sums.total_sales, 0) as total_sales_betting,
coalesce(betting_sums.total_bsi, 0) as total_bsi_betting,
coalesce(virtual_sums.total_sales, 0) as total_sales_virtual,
coalesce(virtual_sums.total_bsi, 0) as total_bsi_virtual,
coalesce(cash_sums.deposit_limit_exceeded_count, 0) as total_num_of_exceeded_cash_deposits,
coalesce(cash_sums.withdraw_limit_exceeded_count, 0) as total_num_of_exceeded_cash_withdrawals
from terminal_locations
join betting_sums on betting_sums.terminal_public_id = terminal_locations.terminal_public_id and betting_sums.location_id = terminal_locations.location_id
join virtual_sums on virtual_sums.terminal_public_id = terminal_locations.terminal_public_id and virtual_sums.location_id = terminal_locations.location_id
join cash_sums on cash_sums.terminal_public_id = terminal_locations.terminal_public_id and cash_sums.location_id = terminal_locations.location_id
order by terminal_public_id, location_id;