<?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
    {
        // 1) 심사 점검표 마스터
        Schema::create('audit_checklists', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('tenant_id')->comment('테넌트ID');
            $table->smallInteger('year')->unsigned()->comment('연도');
            $table->tinyInteger('quarter')->unsigned()->comment('분기 1~4');
            $table->string('type', 30)->default('standard_manual')->comment('심사유형');
            $table->string('status', 20)->default('draft')->comment('draft/in_progress/completed');
            $table->json('options')->nullable()->comment('추가 설정');
            $table->unsignedBigInteger('created_by')->nullable()->comment('생성자');
            $table->unsignedBigInteger('updated_by')->nullable()->comment('수정자');
            $table->unsignedBigInteger('deleted_by')->nullable()->comment('삭제자');
            $table->timestamps();
            $table->softDeletes();

            $table->unique(['tenant_id', 'year', 'quarter', 'type'], 'uq_audit_checklists_tenant_period');
            $table->index(['tenant_id', 'status'], 'idx_audit_checklists_status');
            $table->foreign('tenant_id')->references('id')->on('tenants');
        });

        // 2) 점검표 카테고리
        Schema::create('audit_checklist_categories', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('tenant_id')->comment('테넌트ID');
            $table->unsignedBigInteger('checklist_id')->comment('점검표ID');
            $table->string('title', 200)->comment('카테고리명');
            $table->unsignedInteger('sort_order')->default(0)->comment('정렬순서');
            $table->json('options')->nullable();
            $table->timestamps();

            $table->index(['checklist_id', 'sort_order'], 'idx_audit_categories_sort');
            $table->foreign('checklist_id')->references('id')->on('audit_checklists')->onDelete('cascade');
        });

        // 3) 점검표 세부 항목
        Schema::create('audit_checklist_items', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('tenant_id')->comment('테넌트ID');
            $table->unsignedBigInteger('category_id')->comment('카테고리ID');
            $table->string('name', 200)->comment('항목명');
            $table->text('description')->nullable()->comment('항목 설명');
            $table->boolean('is_completed')->default(false)->comment('완료여부');
            $table->timestamp('completed_at')->nullable()->comment('완료일시');
            $table->unsignedBigInteger('completed_by')->nullable()->comment('완료처리자');
            $table->unsignedInteger('sort_order')->default(0)->comment('정렬순서');
            $table->json('options')->nullable();
            $table->timestamps();

            $table->index(['category_id', 'sort_order'], 'idx_audit_items_sort');
            $table->index(['category_id', 'is_completed'], 'idx_audit_items_completed');
            $table->foreign('category_id')->references('id')->on('audit_checklist_categories')->onDelete('cascade');
            $table->foreign('completed_by')->references('id')->on('users')->onDelete('set null');
        });

        // 4) 기준 문서 연결
        Schema::create('audit_standard_documents', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('tenant_id')->comment('테넌트ID');
            $table->unsignedBigInteger('checklist_item_id')->comment('점검항목ID');
            $table->string('title', 200)->comment('문서명');
            $table->string('version', 20)->nullable()->comment('버전');
            $table->date('date')->nullable()->comment('시행일');
            $table->unsignedBigInteger('document_id')->nullable()->comment('EAV 파일 FK');
            $table->json('options')->nullable();
            $table->timestamps();

            $table->index('checklist_item_id', 'idx_audit_std_docs_item');
            $table->foreign('checklist_item_id')->references('id')->on('audit_checklist_items')->onDelete('cascade');
            $table->foreign('document_id')->references('id')->on('documents')->onDelete('set null');
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('audit_standard_documents');
        Schema::dropIfExists('audit_checklist_items');
        Schema::dropIfExists('audit_checklist_categories');
        Schema::dropIfExists('audit_checklists');
    }
};
