80 lines
4.7 KiB
PHP
80 lines
4.7 KiB
PHP
|
<?php
|
|||
|
|
|
|||
|
|
use Hyperf\Database\Schema\Schema;
|
|||
|
|
use Hyperf\Database\Schema\Blueprint;
|
|||
|
|
use Hyperf\Database\Migrations\Migration;
|
|||
|
|
|
|||
|
|
return new class extends Migration
|
|||
|
|
{
|
|||
|
|
/**
|
|||
|
|
* Run the migrations.
|
|||
|
|
*/
|
|||
|
|
public function up(): void
|
|||
|
|
{
|
|||
|
|
Schema::create('refunds', function (Blueprint $table) {
|
|||
|
|
$table->bigIncrements('id')->comment('主键');
|
|||
|
|
$table->integer('company_id')->comment('公司 ID');
|
|||
|
|
$table->integer('platform_id')->comment('平台 ID');
|
|||
|
|
$table->integer('store_id')->comment('店铺 ID');
|
|||
|
|
$table->bigInteger('order_id')->nullable()->default(null)->comment('关联本地 orders 表 ID');
|
|||
|
|
$table->text('platform_order_id')->comment('平台订单 ID');
|
|||
|
|
$table->text('platform_refund_id')->comment('平台退款单 ID');
|
|||
|
|
$table->integer('refund_status_id')->comment('退款状态 ID');
|
|||
|
|
$table->integer('refund_type_id')->default(1)->comment('退款类型：1 仅退款 2 退货退款 3 补偿退款');
|
|||
|
|
$table->text('reason')->nullable()->default(null)->comment('退款原因');
|
|||
|
|
$table->double('refund_amount', 10, 2)->default(0)->comment('退款金额');
|
|||
|
|
$table->double('freight_refund', 10, 2)->default(0)->comment('退运费金额');
|
|||
|
|
$table->double('refund_total', 10, 2)->default(0)->comment('退款合计金额，为退款金额+退运费金额，为主要参与聚合计算的字段');
|
|||
|
|
$table->text('currency')->default('CNY')->comment('币种');
|
|||
|
|
$table->text('buyer_user_id')->nullable()->default(null)->comment('平台买家 ID');
|
|||
|
|
$table->timestampTz('order_created_date')->comment('关联订单的创建时间');
|
|||
|
|
$table->timestampTz('order_paid_date')->comment('关联订单的付款时间');
|
|||
|
|
$table->timestampTz('created_date')->comment('退款单在平台的创建时间');
|
|||
|
|
$table->timestampTz('updated_date')->nullable()->default(null)->comment('退款单在平台的更新时间');
|
|||
|
|
$table->timestampTz('completed_date')->nullable()->default(null)->comment('退款完成时间');
|
|||
|
|
$table->jsonb('raw')->comment('平台返回的原始数据');
|
|||
|
|
$table->jsonb('ext')->nullable()->default(null)->comment('扩展字段');
|
|||
|
|
$table->char('hash', 32)->comment('raw 字段的 MD5 哈希值，用于检测数据变化');
|
|||
|
|
$table->timestampsTz();
|
|||
|
|
|
|||
|
|
// 索引
|
|||
|
|
// company_id 已被 (company_id, created_date) 复合索引覆盖
|
|||
|
|
// store_id 已被 unique(store_id, platform_refund_id) 覆盖
|
|||
|
|
// refund_status_id、refund_type_id 已被聚合复合索引的最左前缀覆盖
|
|||
|
|
$table->index('platform_id');
|
|||
|
|
$table->index('order_id');
|
|||
|
|
$table->index('platform_order_id');
|
|||
|
|
$table->index('created_date');
|
|||
|
|
$table->index('updated_date');
|
|||
|
|
$table->index('completed_date');
|
|||
|
|
|
|||
|
|
// 联合唯一索引：店铺 + 平台退款单号
|
|||
|
|
$table->unique(['store_id', 'platform_refund_id'], 'refunds_store_platform_refund_unique');
|
|||
|
|
|
|||
|
|
// 复合索引：优化按公司+币种查询退款列表并按时间排序
|
|||
|
|
$table->index(['company_id', 'currency', 'created_date'], 'refunds_company_currency_created_idx');
|
|||
|
|
|
|||
|
|
});
|
|||
|
|
|
|||
|
|
// 复合索引：优化按状态+类型+时间维度的金额聚合查询，INCLUDE(refund_total) 实现 Index-Only Scan
|
|||
|
|
Schema::getConnection()->statement('CREATE INDEX refunds_status_type_currency_order_created_idx ON refunds (refund_status_id, refund_type_id, currency, order_created_date) INCLUDE (refund_total)');
|
|||
|
|
Schema::getConnection()->statement('CREATE INDEX refunds_status_type_currency_order_paid_idx ON refunds (refund_status_id, refund_type_id, currency, order_paid_date) INCLUDE (refund_total)');
|
|||
|
|
Schema::getConnection()->statement('CREATE INDEX refunds_status_type_currency_completed_idx ON refunds (refund_status_id, refund_type_id, currency, completed_date) INCLUDE (refund_total)');
|
|||
|
|
|
|||
|
|
// 为 jsonb 字段创建 GIN 索引（PostgreSQL）
|
|||
|
|
Schema::getConnection()->statement('CREATE INDEX refunds_raw_gin_idx ON refunds USING gin (raw)');
|
|||
|
|
Schema::getConnection()->statement('CREATE INDEX refunds_ext_gin_idx ON refunds USING gin (ext)');
|
|||
|
|
|
|||
|
|
// 表注释
|
|||
|
|
Schema::getConnection()->statement("COMMENT ON TABLE refunds IS '退款记录表，根据平台特点进行数据的写入，如果平台的原始数据就是售后单和子项，则使用 refund_items 表来记录'");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* Reverse the migrations.
|
|||
|
|
*/
|
|||
|
|
public function down(): void
|
|||
|
|
{
|
|||
|
|
Schema::dropIfExists('refunds');
|
|||
|
|
}
|
|||
|
|
};
|