bot-28/database/migrations/2026_11_11_190000_start_daily_backflow.php

<?php

use App\Support\BackflowPeriod;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

return new class extends Migration {
    public function up(): void
    {
        if (!Schema::hasColumn('backflow', 'daily_date')) {
            Schema::table('backflow', function (Blueprint $table) {
                // Keep legacy monthly rows and enforce one row per member for each new daily period.
                $table->string('daily_date', 10)->nullable()->virtualAs('CASE WHEN LENGTH(date) = 10 THEN date END');
            });
        }
        if (!Schema::hasIndex('backflow', 'backflow_member_daily_unique')) {
            Schema::table('backflow', function (Blueprint $table) {
                $table->unique(['member_id', 'daily_date'], 'backflow_member_daily_unique');
            });
        }
        if (!Schema::hasIndex('backflow', 'backflow_status_date_index')) {
            Schema::table('backflow', function (Blueprint $table) {
                $table->index(['status', 'date'], 'backflow_status_date_index');
            });
        }
        $now = now(BackflowPeriod::TIMEZONE);
        DB::table('share_system_state')->insertOrIgnore([
            'key' => BackflowPeriod::DAILY_FROM_KEY,
            'value' => $now->copy()->addDay()->toDateString(),
            'created_at' => $now,
            'updated_at' => $now,
        ]);
    }

    public function down(): void
    {
        if (DB::table('backflow')->whereNotNull('daily_date')->exists()) {
            throw new RuntimeException('已有日回水记录，不能自动恢复月回水');
        }
        DB::table('share_system_state')->where('key', BackflowPeriod::DAILY_FROM_KEY)->delete();
        Schema::table('backflow', function (Blueprint $table) {
            $table->dropIndex('backflow_status_date_index');
            $table->dropUnique('backflow_member_daily_unique');
            $table->dropColumn('daily_date');
        });
    }
};