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

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration {
    public function up(): void
    {
        Schema::table('agents', function (Blueprint $t) { $t->string('language', 8)->default('zh'); });
        Schema::create('site_messages', function (Blueprint $t) {
            $t->engine = 'InnoDB'; $t->id();
            $t->string('category', 24)->default('internal');
            $t->string('recipient_type', 24); $t->json('recipient_ids')->nullable();
            $t->dateTime('send_at'); $t->dateTime('sent_at')->nullable(); $t->dateTime('retracted_at')->nullable();
            $t->string('status', 16)->default('scheduled');
            $t->unsignedBigInteger('sender_id'); $t->string('sender_name', 100);
            $t->string('request_id', 64); $t->char('request_hash', 64);
            $t->unsignedInteger('recipient_count')->default(0); $t->unsignedInteger('skipped_count')->default(0);
            $t->timestamps();
            $t->unique(['sender_id', 'request_id'], 'site_message_request');
            $t->index(['status', 'send_at'], 'site_message_due');
        });
        Schema::create('site_message_translations', function (Blueprint $t) {
            $t->engine = 'InnoDB'; $t->id(); $t->unsignedBigInteger('message_id');
            $t->string('language', 8); $t->string('title', 500); $t->string('subtitle', 500)->default(''); $t->text('content');
            $t->unique(['message_id', 'language'], 'site_message_translation');
        });
        Schema::create('site_message_recipients', function (Blueprint $t) {
            $t->engine = 'InnoDB'; $t->id(); $t->unsignedBigInteger('message_id');
            $t->string('recipient_kind', 8); $t->unsignedBigInteger('recipient_id'); $t->string('language', 8);
            $t->dateTime('read_at')->nullable(); $t->dateTime('created_at');
            $t->unique(['message_id', 'recipient_kind', 'recipient_id'], 'site_message_delivery');
            $t->index(['recipient_kind', 'recipient_id', 'read_at'], 'site_message_inbox');
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('site_message_recipients'); Schema::dropIfExists('site_message_translations'); Schema::dropIfExists('site_messages');
        Schema::table('agents', function (Blueprint $t) { $t->dropColumn('language'); });
    }
};