<?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_production_daily', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('tenant_id');
            $table->date('stat_date');

            // 작업지시
            $table->unsignedInteger('wo_created_count')->default(0)->comment('신규 작업지시');
            $table->unsignedInteger('wo_completed_count')->default(0)->comment('완료 작업지시');
            $table->unsignedInteger('wo_in_progress_count')->default(0)->comment('진행중');
            $table->unsignedInteger('wo_overdue_count')->default(0)->comment('납기 초과');

            // 생산량
            $table->decimal('production_qty', 18, 2)->default(0)->comment('생산 수량');
            $table->decimal('defect_qty', 18, 2)->default(0)->comment('불량 수량');
            $table->decimal('defect_rate', 5, 2)->default(0)->comment('불량률 (%)');

            // 작업 효율
            $table->decimal('planned_hours', 10, 2)->default(0)->comment('계획 공수');
            $table->decimal('actual_hours', 10, 2)->default(0)->comment('실적 공수');
            $table->decimal('efficiency_rate', 5, 2)->default(0)->comment('효율 (%)');

            // 작업자
            $table->unsignedInteger('active_worker_count')->default(0);
            $table->unsignedInteger('issue_count')->default(0)->comment('발생 이슈 수');

            // 납기
            $table->unsignedInteger('on_time_delivery_count')->default(0);
            $table->unsignedInteger('late_delivery_count')->default(0);
            $table->decimal('delivery_rate', 5, 2)->default(0)->comment('납기준수율 (%)');

            $table->timestamps();

            $table->unique(['tenant_id', 'stat_date'], 'uk_tenant_date');
            $table->index('stat_date');
        });
    }

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