# VIP系統規格書
[TOC]
## 需求說明
### 階級方案
#### 靜態階級
數據庫初始時，靜態生成 <span style="color:#808080">**10**</span> 個階級．
- 禮包識別碼規則
> 組別階級編號 = `id`(自增鍵) % 10
以 `vip_gifts_{組別階級編號}` 為禮包唯一識別碼．
- 修改週期數據
複製當前週期對應的系列階級，修改並於插入數據庫作為下一週期的數據．
- 新增週期數據
各週期的組別階級數據為常量，不允許新增．
#### 動態階級
- 禮包識別碼規則
> 門檻權重 = 儲值門檻 + 有效投注
> 階級 = 升冪排序索引(門檻權重&id)
以 `vip_gifts_{階級}` 為禮包唯一識別碼．
- 修改週期數據
複製當前週期對應的系列階級，修改並於插入數據庫作為下一週期的數據．
- 新增週期數據
複製當前週期對應的系列階級，新增並於插入數據庫作為下一週期的數據．
<details>
<summary><b>週期迭代情境說明</b></summary>
> 第一個週期
> |id|*階級*|儲值|投注|*權重*|
> |-|-|-|-|-|
> |4|1|100|300|400|
> |2|2|150|350|500|
> |3|3|200|400|600|
> |1|4|100|500|600|
> |5|5|250|450|700|
假設玩家在第一個週期達到`階級二`，結算時將派發`階級一`與`階級二`對應的禮包內容．
> 第二個週期（相較第一個週期，在原本`階級一`與`階級二`間插入一個新階級）
> |id|*階級*|儲值|投注|*權重*|
> |-|-|-|-|-|
> |4|1|100|300|400|
> |6|2|200|250|450|
> |2|3|150|350|500|
> |3|4|200|400|600|
> |1|5|100|500|600|
> |5|6|250|450|700|
第二個週期結算時，由於`階級一`與`階級二`對應的禮包內容已在第一週期派發，故無法再次派發，此時若玩家達成`階級三`的門檻時，將會派發`階級三`對應的禮包內容．
</details>
## API規格
### 檢索階級門檻
> 各階級有效投注門檻、各階級儲值門檻、各階級等級．
### 檢索階級狀態
> 當前階級、週期內有效投注、週期內儲值．
### VIP前端API
[連結](https://hackmd.io/wt94MYY9RJmta2fN_qnXIQ?both)
## 行為流程圖
### 等級提升
### 降級處置
### 週期設置
### 禮包派發
## 數據庫表
### 狀態表(vip_stats)
#### 數據結構
| user_id | deposit | valid_bet | tier_id | tier_id_achieved | created_at | updated_at |
|---------|---------|-----------|-------|----------------|--------------------|--------------------|
| 1001 | 650 | 720 | 1 | 2 | 2024-02-26 08:00:00| 2024-02-26 08:00:00|
| 1002 | 420 | 890 | 2 | 2 | 2024-02-25 11:30:00| 2024-02-25 11:30:00|
| 1003 | 880 | 250 | 2 | 3 | 2024-02-24 09:45:00| 2024-02-24 09:45:00|
| 1004 | 210 | 510 | 3 | 3 | 2024-02-23 06:20:00| 2024-02-23 06:20:00|
| 1005 | 790 | 320 | 3 | 4 | 2024-02-22 13:10:00| 2024-02-22 13:10:00|
> 透過 `level_achived`(達成等級) 與 `level`(目前等級) 判斷玩家是否降級過．
- Laravel Migration
```PHP
Schema::create('vip_stats', function (Blueprint $table) {
$table->string('user_id', 32);
$table->decimal('deposit', 16, 4)
->default(0.0000)
->comment('歷史累積儲值');
$table->decimal('valid_bet', 16, 4)
->default(0.0000)
->comment('歷史累積效投');
$table->unsignedtinyInteger('tier_id')->comment('目前階級id');
$table->unsignedtinyInteger('tier_id_achieved')->comment('達成階級id');
$table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
$table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));
$table->primary('user_id');
$table->index('user_id');
$table->index('updated_at');
});
```
### 週期表(vip_cycles)
會員週期與版主週期統計數據．
#### 數據結構
| id | user_id | deposit | valid_bet | cycle_begin_date | cycle_until_date | created_at | updated_at |
|----|---------|---------|-----------|---------------------|---------------------|----------------------|----------------------|
| 1 | 1001 | 500 | 700 | 2024-03-01 | 2024-03-30| 2024-02-28| 2024-02-28|
| 2 | 1002 | 1000 | 1500 | 2024-03-01| 2024-03-30 | 2024-02-29| 2024-02-29|
| 3 | 1003 | 1500 | 2000 | 2024-03-01| 2024-03-30| 2024-03-01| 2024-03-01|
| 4 | 1004 | 2000 | 2500 | 2024-03-01 | 2024-03-30| 2024-03-01| 2024-03-02|
| 5 | 1005 | 2500 | 3000 | 2024-03-01 | 2024-03-30 | 2024-03-01 | 2024-03-03 |
| 6 | 1004 | 2500 | 3000 | 2024-04-01 | 2024-04-30 | 2024-04-03 | 2024-03-03 |
| 7 | 1005 | 2500 | 3000 | 2024-04-01 | 2024-04-30 | 2024-04-03 | 2024-04-03 |
- Laravel Migration
```PHP
Schema::create('vip_cycles', function (Blueprint $table) {
$table->increments('id');
$table->string('user_id', 32);
$table->decimal('deposit', 16, 4)
->default(0.0000)
->comment('當週累積儲值');
$table->decimal('valid_bet', 16, 4)
->default(0.0000)
->comment('當週累積效投');
$table->date('cycle_begin_date')->comment('週期起始日');
$table->date('cycle_until_date')->comment('週期結束日');
$table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
$table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));
$table->index('user_id');
$table->index(['cycle_begin_at', 'cycle_until_at']);
$table->index('updated_at');
});
```
### 階級表(vip_tiers)
定義階級所需要的儲值門檻、投注門檻．
#### 數據結構
| id | name | upgrade_deposit | upgrade_valid_bet | keep_deposit | keep_valid_bet | enable | created_at| updated_at|
|----|--------------------|---------|-----------|---------|-----------|-----------|--------------------|--------------------|
| 1 | 階級1| 300 | 400 | 300 | 400 | ture | 2024-02-20 08:00:00| 2024-02-20 08:00:00|
| 2 | 階級2| 350 | 500 | 350 | 500 | false | 2024-02-20 08:00:00| 2024-02-20 08:00:00|
| 3 | 階級3| 400 | 600 | 400 | 600 | ture | 2024-02-20 08:00:00| 2024-02-20 08:00:00|
| 4 | 階級4| 500 | 600 | 500 | 600 | ture | 2024-02-20 08:00:00| 2024-02-20 08:00:00|
| 5 | 階級5| 450 | 700 | 450 | 700 | ture | 2024-02-20 08:00:00| 2024-02-20 08:00:00|
| 6 | 階級6| 300 | 400 | 300 | 400 | ture | 2024-02-22 13:10:00| 2024-02-22 13:10:00|
| 7 | 階級7| 350 | 500 | 350 | 500 | ture | 2024-02-22 13:10:00| 2024-02-22 13:10:00|
| 8 | 階級8| 400 | 600 | 400 | 600 | false | 2024-02-22 13:10:00| 2024-02-22 13:10:00|
| 9 | 階級9| 500 | 600 | 500 | 600 | ture | 2024-02-22 13:10:00| 2024-02-22 13:10:00|
| 10 | 階級10| 450 | 700 | 450 | 700 | false | 2024-02-22 13:10:00| 2024-02-22 13:10:00|
- Laravel Migration
```PHP
Schema::create('vip_tiers', function (Blueprint $table) {
$table->string('id', 32)->comment('階級id');
$table->string('name', 32)->comment('名稱');
$table->decimal('upgrade_deposit', 16, 4)
->default(0.0000)
->comment('升級儲值門檻');
$table->decimal('upgrade_valid_bet', 16, 4)
->default(0.0000)
->comment('升級效投門檻');
$table->decimal('keep_deposit', 16, 4)
->default(0.0000)
->comment('保級儲值門檻');
$table->decimal('keep_valid_bet', 16, 4)
->default(0.0000)
->comment('保級效投門檻');
$table->boolean('enable')->default(false)->comment('開關');
$table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
$table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));
$table->primary('id');
$table->unique('upgrade_deposit');
$table->unique('upgrade_valid_bet');
$table->index(['id', 'enable']);
$table->index('updated_at');
});
```
### 日誌表(vip_logs)
1. 異動VIP等級時需要紀錄
2. 派發3種禮包時紀錄
3. VIP門檻升降級
4. 手動調整VIP等級
#### 數據結構
| id | user_id | type | current_tier_id | achieved_tier_ids | log_date | handler_id | remark | notice | created_at| updated_at|
|----|----|----|--------|---------|-------|-------|-------|-------|--------------------|--------------------|
|01hrrxnaeehy6nd58bzbf0r54n|01hcyg20p3v4npn7pc356xvyjk|register|1|[]|2024-03-19|01hcyg20p3v4npn7pc356xvyjl||no|2024-03-19 08:00:00| 2024-03-19 08:00:00|
|01hrrxnaeehy6nd58bzbf0r54k|01hcyg20p3v4npn7pc356xvyjk|system-upgrade|2|[2]|2024-03-19|01hcyg20p3v4npn7pc356xvyjl||no|2024-03-19 12:00:00| 2024-03-19 12:00:00|
|01hrrxnaeehy6nd58bzbf0r54l|01hcyg20p3v4npn7pc356xvyjk|manual-upgrade|4|[3,4]|2024-03-19|01hcyg20p3v4npn7pc356xvyjl||no|2024-03-19 14:00:00| 2024-03-19 14:00:00|
|01hrrxnaeehy6nd58bzbf0r54a|01hcyg20p3v4npn7pc356xvyjk|downgrade|1|[]|2024-03-30|01hcyg20p3v4npn7pc356xvyjl||no|2024-03-30 12:00:00| 2024-03-30 12:00:00|
- Laravel Migration
```PHP
Schema::create('vip_logs', function (Blueprint $table) {
// [PK] 資料識別碼
$table->string('id', 32)->comment('資料識別碼');
// 會員ID
$table->string('user_id', 32)->comment('會員ID, mapping users.id');
$table->string('type', 32)->comment('紀錄類型');
$table->unsignedtinyInteger('current_tier_id')->comment('目前階級id');
$table->longtext('achieved_tier_ids')
->default('[]')
->comment('升降級的tier id or 禮包id');
$table->string('log_date')->comment('紀錄時間');
$table->string('handler_id', 32)->comment('操作人員');
// 備註
$table->text('remark')->default('')->comment('備註');
// 會員推波通知開關 (yes已通知, no未通知)
$table->enum('notice', ['yes', 'no'])->default('no')->comment('會員推波通知開關 (yes已通知, no未通知)');
$table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
$table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));
// === 索引 ===
// 指定主鍵索引
$table->primary(['id']);
$table->index('user_id');
$table->index('type');
$table->index('updated_at');
});
```
#### 站台/會員週期相關設置
config
1.起始日期
2.週期天數
#### 業務邏輯
- VIP階級推算
- SQL 語法
```SQL
SELECT
ROW_NUMBER() OVER(
ORDER BY (deposit + valid_bet) ASC, id ASC
) AS level,
id,
deposit,
valid_bet,
(deposit + valid_bet) AS weight
FROM your_table_name
ORDER BY weight ASC, id ASC;
```
- Laravel 語法
```PHP=
$results = YourTableName::select([
DB::raw("ROW_NUMBER() OVER(
ORDER BY (deposit + valid_bet) ASC, id ASC
) AS level"),
'valid_bet',
'deposit',
DB::raw('(deposit + valid_bet) AS weight')
])
->orderBy('weight', 'asc')
->orderBy('id', 'asc')
->get();
```
- 預期結果
|level|deposit|valid_bet|weight|
|-|-|-|-|
|1|100|300|400|
|2|150|350|500|
|3|200|400|600|
|4|100|500|600|
|5|250|450|700|
### 活動對應表(vip_activity_relation)
VIP與活動關聯的表
#### 數據結構
|id|vip_tiers_id|activity_id|type|point|rebate_table|rebate_mode|threshold_gift_multiple|revenge_limit|revenge_frequency_days|gift_limit|created_at|updated_at|
|-------|----|---------|-----|------|----|------|---------|---|----|----|----------|---------|
|01e4nb76nq2440vpmjf6b4dhva|01e4nb76nq2440vpmjf6b4dhvb|01e4nb76nq2440vpmjf6b4dhvd|upgrade|[]|null|50|1|0|0|0|2024-02-20 08:00:00| 2024-02-20 08:00:00|
|01e66hv9j1e8b8rjde9xtak73a|01e66hv9j1e8b8rjde9xtak73b|01e66hv9j1e8b8rjde9xtak731|revenge|[]|null|60|1|100|20|0|2024-02-20 08:00:00| 2024-02-20 08:00:00|
|01e66mp01m3w9crbbb3aaff0ka|01e66mp01m3w9crbbb3aaff0kb|01e66mp01m3w9crbbb3aaff0kd|rebate|{"sa_gaming":{"bac":{"general":"9"}}}|station|0|1|0|0|1000|2024-02-20 08:00:00| 2024-02-20 08:00:00|
- Laravel Migration
```PHP
Schema::create('vip_activity_relation', function (Blueprint $table) {
$table->string('id', 32);
$table->string('vip_tier_id', 32)->comment('VIP階級ID');
$table->string('activity_id', 32)->comment('活動ID');
$table->string('type', 16)->comment('類型 (upgrade=升級, revenge=復仇金, rebate=返水)');
$table->decimal('point', 16, 4)
->default(0.0000)
->comment('獎金');
$table->longtext('rebate_table')
->default('[]')
->comment('遊戲返水表');
$table->string('rebate_mode', 16)
->nullable()
->default(null)
->comment('返水模式');
$table->decimal('revenge_limit', 16, 4)
->default(0.0000)
->comment('復仇金最低額');
$table->unsignedtinyInteger('revenge_frequency_days')->default(0)->comment('復仇金發送頻率');
$table->decimal('threshold_gift_multiple', 16, 4)
->default(0.0000)
->comment('洗碼倍數(禮金)');
$table->decimal('gift_limit', 16, 4)
->default(0.0000)
->comment('禮金上限');
$table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
$table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));
$table->primary('id');
$table->index(['activity_id', 'vip_tier_id']);
$table->index('type');
$table->index('updated_at');
});
```
### VIP活動條件表(vip_gift_condition)
由vip_activity_relation衍生出來的活動條件．
#### 數據結構
|id|is_daily_login|total_valid_bet|station_valid_bet|revenge_limit|revenge_frequency_days|created_at|updated_at|
|---|---|---|---|---|---|---|---|
|01e4nb76nq2440vpmjf6b4dhva|true|null|null|null|null|2024-02-20 08:00:00| 2024-02-20 08:00:00|
|01e4nb76nq2440vpmjf6b4dhvb|null|100|null|null|null|2024-02-20 08:00:00| 2024-02-20 08:00:00|
|01e4nb76nq2440vpmjf6b4dhvc|null|null|1000|null|null|2024-02-20 08:00:00| 2024-02-20 08:00:00|
- Laravel Migration
```php
Schema::create('vip_gift_condition', function (Blueprint $table) {
$table->string('id', 32);
$table->boolean('is_daily_login')->nullable()->comment('每日登入');
$table->decimal('total_valid_bet', 16, 4)->nullable()->comment('總有效投注');
$table->longtext('station_valid_bet')->nullable()->comment('特定遊戲館有效投注');
$table->decimal('revenge_limit', 16, 4)->nullable()->comment('復仇金最低額');
$table->unsignedtinyInteger('revenge_frequency_days')->nullable()->comment('復仇金發送頻率');
$table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
$table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));
$table->primary('id');
$table->index('updated_at');
});
```
### 活動彩卷表(activity_lottery)
活動彩卷包含所屬活動、週期、票卷類型、關聯獲得獎項．
#### 數據結構
|id|user_id|activity_id|activity_sheet_id|gift_id|gift_model|ticket_type|prize_id|expired_at|used_at|handle_by|created_at|updated_at|
|---|---|---|---|---|---|---|---|---|---|---|---|---|
|01e66mp01m3w9crbbb3aaff0kb|01e66mp01m3w9crbbb3aafsjro|01e66mp01m3w9crbbb3aaff8po|01e66mp01m3w9crbbb3aaffjey|null|null|null|null|2999-01-01 00:00:00|null|01e66mp01m3w9crbbb3aajue8i|2024-02-20 08:00:00| 2024-02-20 08:00:00|
- Laravel Migration
```php
Schema::create('activity_lottery', function (Blueprint $table) {
$table->string('id', 32)->comment('獎券唯一值');
$table->string('user_id', 32)->comment('會員id');
$table->string('activity_id', 32)->nullable()->comment('對應活動id');
$table->string('activity_sheet_id', 32)->nullable()->comment('對應活動期數id');
$table->string('gift_id', 32)->nullable()->comment('對應禮包資訊id');
$table->string('gift_model', 16)->nullable()->comment('對應禮包的model');
$table->string('ticket_type', 8)->nullable()->comment('票券類型');
$table->string('prize_id', 32)->nullable()->comment('對應獎項id');
$table->datetime('expired_at')->default('2999-01-01 00:00:00')->comment('獎券到期時間');
$table->datetime('used_at')->nullable()->comment('獎券抽獎時間');
$table->string('handle_by', 32)->nullable()->comment('派發人員');
$table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
$table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));
$table->primary('id');
$table->index('user_id');
$table->index(['activity_id', 'activity_sheet_id']);
$table->index('created_at');
$table->index('updated_at');
});
```
## 開發資訊
### setting 設定參數
> 取得方式
```php=
config('platform.vip_cycles')
```
> 參數內容
```php=
'vip_cycles' => [
'days' => -1,
'begin_date' => '',
'type' => '',
]
```
### Gitlab分支
> vip_dev
### 開發測試站
> aa 站
### 前端頁面
> [here](https://upspeedt.com/)
### 版主端頁面
> [here](https://ctup.upspeedt.com/zh-Hant)
> 帳密同ab
==關閉自動部署，需進機器自行reset==
## 系統Q&A
1. 異常補發VIP禮包時是否需要以前一個週期的規則？
2. 版主套用會員週期時，由於各用戶週期不同，要如何統計上期人數？
-- 設定成會員週期不用顯示上期人數
3. 每日結算時間是否可以訂於中午12:00？
-- 可設定於中午12:00
4. 週期內異動VIP階級表時，是否會影響當下週期的邏輯判斷？
## 測試要點
## 小記錄
1. 開一張附表對應 activities 與 vip
2. sheet 表為排程自動產生
* `activity:manual-checkout->checkoutActivityPeriod`
* `activity:rebate:sheet->createRebateActivitySheetByDateTime`
3. 一個VIP會產生三個禮包：返水、升級、復仇
Sign in with Wallet
Connect another wallet