# Report sql MST
## Note

- replace '2022-01-01' thành biến số from
- replace '2023-01-01' thành biến số to

## Create MATERIALIZED VIEW

### Created

Step tạo table view
1. CREATE MATERIALIZED VIEW {{table_name}} as
Câu select
2. Đánh index
CREATE UNIQUE INDEX idx_{{table_name}} ON {{table_name}}(giá trị unque 1,giá trị unque 2...);
3. Refresh:
REFRESH MATERIALIZED VIEW CONCURRENTLY {{table_name}};

### Drop
1.Drop
DROP MATERIALIZED VIEW {{table_name}};
2.Created
CREATE MATERIALIZED VIEW {{table_name}} as

Câu select
3. Đánh index
CREATE UNIQUE INDEX idx_{{table_name}} ON {{table_name}}(giá trị unque 1,giá trị unque 2...);
4. Refresh:
REFRESH MATERIALIZED VIEW CONCURRENTLY {{table_name}};
### Cronjob
REFRESH MATERIALIZED VIEW CONCURRENTLY {{table_name}};

### Danh sách table cần tạo
#### offchain_transaction_log_view
- Select

select day,owner_address,type_name,sum(mst_amount) as bscs,sum(bscd_amount) as bscd from 
(select date(time) as day,time,owner_address,type_name,status,mst_amount,bscd_amount from offchain_transaction_log) as tbl 
group by day,type_name,owner_address,status
order by day desc, type_name desc  

- index: day,type_name,owner_address,status

- Created table view

CREATE MATERIALIZED VIEW offchain_transaction_log_view as

select day,owner_address,type_name,status,sum(mst_amount) as bscs,sum(bscd_amount) as bscd from 
(select date(time) as day,time,owner_address,type_name,status,mst_amount,bscd_amount from offchain_transaction_log where type_name <> 'SYNC_DAILY') as tbl 
group by day,type_name,owner_address,status
order by day desc, type_name desc 

;

CREATE UNIQUE INDEX index_offchain_transaction_log_view ON offchain_transaction_log_view(day,type_name,owner_address,status);

REFRESH MATERIALIZED VIEW CONCURRENTLY offchain_transaction_log_view;

#### adventure_log_view
- Select

select day,character_id,owner_address,sum(mst) as bscs from 
(select date(created_at) as day,character_id,owner_address,COALESCE((reward->>'totalRewardMST')::DECIMAL, 0) as mst from adventure_log) as tbl
group by day,character_id,owner_address
order by day desc, character_id desc 

- index: day,character_id,owner_address

- Created table view

CREATE MATERIALIZED VIEW adventure_log_view as

select day,character_id,owner_address,sum(mst) as bscs from 
(select date(created_at) as day,character_id,owner_address,COALESCE((reward->>'totalRewardMST')::DECIMAL, 0) as mst from adventure_log) as tbl
group by day,character_id,owner_address
order by day desc, character_id desc 

;

CREATE UNIQUE INDEX index_adventure_log_view ON adventure_log_view(day,character_id,owner_address);

REFRESH MATERIALIZED VIEW CONCURRENTLY adventure_log_view;

#### salary_bscd_view
- Select

select 
*,
CASE
  WHEN pool_id = 1 THEN block_available * mining_ratio * reward_multiplier /10000
  ELSE block_available * mining_ratio * reward_multiplier * advantageAttr_salary /100000
  END as salary_bscd,
CASE
  WHEN pool_id = 1 THEN total_worked_block_available * mining_ratio * reward_multiplier /10000
  ELSE total_worked_block_available * mining_ratio * reward_multiplier * advantageAttr_salary /100000
  END as salary_earned
from
(
  select 
  CASE
    WHEN c.level = 1 THEN 1
    WHEN c.level = 2 THEN 2
    WHEN c.level = 3 THEN 4
    WHEN c.level = 4 THEN 8
    WHEN c.level = 5 THEN 16
    WHEN c.level = 6 THEN 25
    WHEN c.level = 7 THEN 50
    WHEN c.level = 8 THEN 100
    WHEN c.level = 9 THEN 150
    WHEN c.level = 10 THEN 200
    END as reward_multiplier,
  CASE 
    WHEN c.advantage = 'Strength' THEN 10 + (c.strength - 85)*5
    WHEN c.advantage = 'Stamina' THEN 10 + (c.stamina - 85)*5
    WHEN c.advantage = 'Intelligence' THEN 10 + (c.intelligence - 85)*5
    WHEN c.advantage = 'Courage' THEN 10 + (c.courage - 85)*5
    WHEN c.advantage = 'Dexterity' THEN 10 + (c.dexterity - 85)*5
    WHEN c.advantage = 'Vitality' THEN 10 + (c.vitality - 85)*5
    END as advantageAttr_salary,
  432000 - remaining_100 + (432000 - remaining_80)*0.8 + (864000 - remaining_5)*0.05 as total_worked_block_available,
  tbl5.*
  from
  (
    select tbl4.*,cc.mining_ratio,earning_5*0.05+earning_80*0.8+earning_100 as block_available
    from
    (
      select *,
      CASE
        WHEN remaining_5 = 0 THEN 0
        WHEN working_block_now - earning_100 - earning_80 < remaining_5 THEN working_block_now - earning_100 - earning_80
        WHEN working_block_now - earning_100 - earning_80 > remaining_5 THEN remaining_5
        END as earning_5
      from
      (
        select *,
        CASE
          WHEN remaining_80 = 0 THEN 0
          WHEN working_block_now - earning_100 < remaining_80 THEN working_block_now - earning_100
          WHEN working_block_now - earning_100 > remaining_80 THEN remaining_80
          END as earning_80
        from
        (
          select *,
          CASE
            WHEN remaining_100 = 0 THEN 0
            WHEN working_block_now < remaining_100 THEN working_block_now
            WHEN working_block_now > remaining_100 THEN remaining_100
            END as earning_100
          from
          (
            select c.token_id,c.status,c.total_worked_block,cw.start_working_block,cw.pool_id,sc.value,
            sc.value::int - cw.start_working_block as working_block_now,
            CASE 
              WHEN 432000 - c.total_worked_block > 0 THEN 432000 - c.total_worked_block
              ELSE 0 
              END as remaining_100,
            CASE 
              WHEN 432000 - c.total_worked_block > 0 THEN 432000
              WHEN 864000 - c.total_worked_block > 0 THEN 864000 - c.total_worked_block
              ELSE 0 
              END as remaining_80,
            CASE 
              WHEN 864000 - c.total_worked_block > 0 THEN 864000
              WHEN 1728000 - c.total_worked_block > 0 THEN 1728000 - c.total_worked_block
              ELSE 0 
              END as remaining_5
            from CHARACTER as c 
            inner join 
            character_work cw 
            on c.id = cw.character_id
            inner join
            setting_config sc
            on 1=1
            where sc.name = 'current_block'
            group by c.token_id,c.level,c.total_worked_block,cw.start_working_block,cw.pool_id,sc.value,c.status
          ) as tbl1
        ) as tbl2
      ) as tbl3
    ) as tbl4
    inner join
    (
      select (data->>'_miningRatio')::INT as mining_ratio from contract_config where name = 'salary_config'
    ) as cc
    on 1=1
  ) as tbl5
  inner join CHARACTER as c 
  on c.token_id = tbl5.token_id
) as tbl6

- index: token_id

- Create table view

CREATE MATERIALIZED VIEW salary_bscd_view as

select 
*,
CASE
  WHEN pool_id = 1 THEN block_available * mining_ratio * reward_multiplier /10000
  ELSE block_available * mining_ratio * reward_multiplier * advantageAttr_salary /100000
  END as salary_bscd,
CASE
  WHEN pool_id = 1 THEN total_worked_block_available * mining_ratio * reward_multiplier /10000
  ELSE total_worked_block_available * mining_ratio * reward_multiplier * advantageAttr_salary /100000
  END as salary_earned
from
(
  select 
  CASE
    WHEN c.level = 1 THEN 1
    WHEN c.level = 2 THEN 2
    WHEN c.level = 3 THEN 4
    WHEN c.level = 4 THEN 8
    WHEN c.level = 5 THEN 16
    WHEN c.level = 6 THEN 25
    WHEN c.level = 7 THEN 50
    WHEN c.level = 8 THEN 100
    WHEN c.level = 9 THEN 150
    WHEN c.level = 10 THEN 200
    END as reward_multiplier,
  CASE 
    WHEN c.advantage = 'Strength' THEN 10 + (c.strength - 85)*5
    WHEN c.advantage = 'Stamina' THEN 10 + (c.stamina - 85)*5
    WHEN c.advantage = 'Intelligence' THEN 10 + (c.intelligence - 85)*5
    WHEN c.advantage = 'Courage' THEN 10 + (c.courage - 85)*5
    WHEN c.advantage = 'Dexterity' THEN 10 + (c.dexterity - 85)*5
    WHEN c.advantage = 'Vitality' THEN 10 + (c.vitality - 85)*5
    END as advantageAttr_salary,
  432000 - remaining_100 + (432000 - remaining_80)*0.8 + (864000 - remaining_5)*0.05 as total_worked_block_available,
  tbl5.*
  from
  (
    select tbl4.*,cc.mining_ratio,earning_5*0.05+earning_80*0.8+earning_100 as block_available
    from
    (
      select *,
      CASE
        WHEN remaining_5 = 0 THEN 0
        WHEN working_block_now - earning_100 - earning_80 < remaining_5 THEN working_block_now - earning_100 - earning_80
        WHEN working_block_now - earning_100 - earning_80 > remaining_5 THEN remaining_5
        END as earning_5
      from
      (
        select *,
        CASE
          WHEN remaining_80 = 0 THEN 0
          WHEN working_block_now - earning_100 < remaining_80 THEN working_block_now - earning_100
          WHEN working_block_now - earning_100 > remaining_80 THEN remaining_80
          END as earning_80
        from
        (
          select *,
          CASE
            WHEN remaining_100 = 0 THEN 0
            WHEN working_block_now < remaining_100 THEN working_block_now
            WHEN working_block_now > remaining_100 THEN remaining_100
            END as earning_100
          from
          (
            select c.token_id,c.status,c.total_worked_block,cw.start_working_block,cw.pool_id,sc.value,
            sc.value::int - cw.start_working_block as working_block_now,
            CASE 
              WHEN 432000 - c.total_worked_block > 0 THEN 432000 - c.total_worked_block
              ELSE 0 
              END as remaining_100,
            CASE 
              WHEN 432000 - c.total_worked_block > 0 THEN 432000
              WHEN 864000 - c.total_worked_block > 0 THEN 864000 - c.total_worked_block
              ELSE 0 
              END as remaining_80,
            CASE 
              WHEN 864000 - c.total_worked_block > 0 THEN 864000
              WHEN 1728000 - c.total_worked_block > 0 THEN 1728000 - c.total_worked_block
              ELSE 0 
              END as remaining_5
            from CHARACTER as c 
            inner join 
            character_work cw 
            on c.id = cw.character_id
            inner join
            setting_config sc
            on 1=1
            where sc.name = 'current_block'
            group by c.token_id,c.level,c.total_worked_block,cw.start_working_block,cw.pool_id,sc.value,c.status
          ) as tbl1
        ) as tbl2
      ) as tbl3
    ) as tbl4
    inner join
    (
      select (data->>'_miningRatio')::INT as mining_ratio from contract_config where name = 'salary_config'
    ) as cc
    on 1=1
  ) as tbl5
  inner join CHARACTER as c 
  on c.token_id = tbl5.token_id
) as tbl6



;

CREATE UNIQUE INDEX index_salary_bscd_view ON salary_bscd_view(token_id);

REFRESH MATERIALIZED VIEW CONCURRENTLY salary_bscd_view;

## sql
### Wallet Onchain
- Không làm được
### Wallet Offchain - Done
- Fee collected BSCS - BSCD

select 

(select sum(mst_amount) * 0.1 
from offchain_transaction_log 
where type_name in ('CHARACTER_MINTED','CHARACTER_UPGRADE') and time BETWEEN '2022-01-01' and '2023-01-01') + 
(select sum(mst_amount) * 0.15 
from offchain_transaction_log 
where type_name in  ('WITHDRAW') and time BETWEEN '2022-01-01' and '2023-01-01') as BSCS
,

(select sum(bscd_amount) * 0.1 
from offchain_transaction_log 
where  type_name in ('CHARACTER_MINTED','CHARACTER_UPGRADE') and time BETWEEN '2022-01-01' and '2023-01-01') + 
(select sum(bscd_amount) * 0.15 
from offchain_transaction_log 
where type_name in  ('WITHDRAW') and time BETWEEN '2022-01-01' and '2023-01-01')
as BSCD

2023/03/24

select 

(select sum(bscs) * 0.1 
from offchain_transaction_log_view 
where type_name in ('CHARACTER_MINTED','CHARACTER_UPGRADE') and day BETWEEN '2022-01-01' and '2023-01-01') + 
(select sum(bscs) * 0.15 
from offchain_transaction_log_view 
where type_name in  ('WITHDRAW') and status = 'done' and day BETWEEN '2022-01-01' and '2023-01-01') as BSCS
,

(select sum(bscd) * 0.1 
from offchain_transaction_log_view 
where  type_name in ('CHARACTER_MINTED','CHARACTER_UPGRADE') and day BETWEEN '2022-01-01' and '2023-01-01') + 
(select sum(bscd) * 0.15 
from offchain_transaction_log_view 
where type_name in  ('WITHDRAW') and status = 'done' and day BETWEEN '2022-01-01' and '2023-01-01')
as BSCD

### Deposited/Withdraw

select sum(mst_amount) FILTER (where type_name ='DEPOSIT') as Deposit_BSCS, sum(bscd_amount) FILTER (where type_name ='DEPOSIT') as Deposit_BSCD,  sum(mst_amount) FILTER (where type_name ='WITHDRAW') as Withdraw_BSCS, sum(bscd_amount) FILTER (where type_name ='WITHDRAW') as Withdraw_BSCD
from offchain_transaction_log 
where time BETWEEN '2022-01-01' and '2023-01-01'

2023/03/24

select sum(mst_amount) FILTER (where type_name ='DEPOSIT') as Deposit_BSCS, sum(bscd_amount) FILTER (where type_name ='DEPOSIT') as Deposit_BSCD,  sum(mst_amount) FILTER (where type_name ='WITHDRAW' and status not in ('waiting_approve','rejected')) as Withdraw_BSCS, sum(bscd_amount) FILTER (where type_name ='WITHDRAW' and status not in ('waiting_approve','rejected')) as Withdraw_BSCD
from offchain_transaction_log 
where time BETWEEN '2022-01-01' and '2023-01-01'

### Gameplay Stat (— Mining Power: ? BSCD/block không làm được) - Done

select sum(mst_amount) FILTER (where type_name ='CLAIM_BATTLE_REWARD') as Claim_battle_BSCS, sum(bscd_amount) FILTER (where type_name ='CLAIM_BATTLE_REWARD') as Claim_battle_BSCD,  
sum(mst_amount) FILTER (where type_name ='USE_PILL') as UseFill_BSCS, sum(bscd_amount) FILTER (where type_name ='USE_PILL') as UseFill_BSCD,
sum(mst_amount) FILTER (where type_name ='REFILL_ENERGY') as Refill_BSCS, sum(bscd_amount) FILTER (where type_name ='REFILL_ENERGY') as Refill_BSCD
from offchain_transaction_log 
where time BETWEEN '2022-01-01' and '2023-01-01'

2023/03/23

select sum(bscs) FILTER (where type_name ='CLAIM_BATTLE_REWARD') as Claim_battle_BSCS, sum(bscd) FILTER (where type_name ='CLAIM_BATTLE_REWARD') as Claim_battle_BSCD,  
sum(bscs) FILTER (where type_name ='USE_PILL') as UseFill_BSCS, sum(bscd) FILTER (where type_name ='USE_PILL') as UseFill_BSCD,
sum(bscs) FILTER (where type_name ='REFILL_ENERGY') as Refill_BSCS, sum(bscd) FILTER (where type_name ='REFILL_ENERGY') as Refill_BSCD
from offchain_transaction_log_view
where day BETWEEN '2022-01-01' and '2023-01-01'

### System

- Daily Active Wallets - Done

select to_char(time + interval '7 hour','YYYY-MM-DD') as day,count(DISTINCT owner_address)  from offchain_transaction_log
where time BETWEEN '2022-01-01' and '2023-01-01'
group by day
order by day desc

2023/03/23

select day,count(1) from (
  select day,owner_address from offchain_transaction_log_view where day BETWEEN '2022-01-01' and '2023-01-01' group by owner_address,day
)j
group by day 
order by day desc

- Total Wallet Connected - Done

select count(DISTINCT wallet_address) from offchain_ledger

2023/03/23

select count(1) from offchain_ledger

- Current working at Mining, chia theo level mỏ - Done

select pool_id,count(DISTINCT character_id) from character_work where status = 'working' group by pool_id order by pool_id asc 

2023/03/23

select pool_id,count(1) from character_work where status = 'working' group by pool_id order by pool_id asc 

- Lượng BSCD mine ra từ các mỏ theo ngày / tuần / tháng - Not Yet
- Total BSCD chưa claim

    select sum(salary_bscd) from salary_bscd_view where status = 'working'

- Total BSCD đã claim theo ngày /tuần tháng

    select sum(bscd_amount) FILTER (where type_name in ('QUIT_WORKING','CLAIM_SALARY_AND_QUIT_WORK','CLAIM_SALARY') ) as Amount_BSCD 
    from offchain_transaction_log 
    where time BETWEEN '2022-01-01' and '2023-01-01'

2023/03/23

    select sum(bscd) FILTER (where type_name in ('QUIT_WORKING','CLAIM_SALARY_AND_QUIT_WORK','CLAIM_SALARY') ) as Amount_BSCD 
    from offchain_transaction_log_view 
    where day BETWEEN '2022-01-01' and '2023-01-01'

- PVE Heroes theo ngày / tuần / tháng -

select to_char(created_at + interval '7 hour','YYYY-MM-DD') as day,count(DISTINCT character_id)  from adventure_log
where created_at BETWEEN '2022-01-01' and '2023-01-01'
group by day
order by day desc

2023/03/23

select day,count(1) from (
  select day,character_id from adventure_log_view where day BETWEEN '2022-01-01' and '2023-01-01' group by character_id,day
)j
group by day 
order by day desc

- Heroes theo level

select level,count(*) from "character" group by level order by level asc