<?php

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

return new class extends Migration
{
    protected $connection = 'sam_stat';

    public function up(): void
    {
        Schema::connection($this->connection)->create('stat_sales_monthly', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('tenant_id');
            $table->smallInteger('stat_year');
            $table->tinyInteger('stat_month');

            // 일일 합산
            $table->unsignedInteger('order_count')->default(0);
            $table->decimal('order_amount', 18, 2)->default(0);
            $table->unsignedInteger('sales_count')->default(0);
            $table->decimal('sales_amount', 18, 2)->default(0);
            $table->unsignedInteger('shipment_count')->default(0);
            $table->decimal('shipment_amount', 18, 2)->default(0);

            // 월간 고유 지표
            $table->unsignedInteger('unique_client_count')->default(0)->comment('거래 고객 수');
            $table->decimal('avg_order_amount', 18, 2)->default(0)->comment('평균 수주 금액');
            $table->unsignedBigInteger('top_client_id')->nullable()->comment('최다 거래 고객');
            $table->decimal('top_client_amount', 18, 2)->default(0);
            $table->decimal('mom_growth_rate', 8, 2)->nullable()->comment('전월 대비 성장률 (%)');
            $table->decimal('yoy_growth_rate', 8, 2)->nullable()->comment('전년동월 대비 (%)');

            $table->timestamps();

            $table->unique(['tenant_id', 'stat_year', 'stat_month'], 'uk_tenant_year_month');
            $table->index(['stat_year', 'stat_month']);
        });
    }

    public function down(): void
    {
        Schema::connection($this->connection)->dropIfExists('stat_sales_monthly');
    }
};
