<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        // Check if table exists first
        if (!Schema::hasTable('m_numbering_formats')) {
            return;
        }

        Schema::table('m_numbering_formats', function (Blueprint $table) {
            // Add SPB Type column (Regular/Non-Regular)
            if (!Schema::hasColumn('m_numbering_formats', 'spb_type')) {
                $table->enum('spb_type', ['Regular', 'Non-Regular', 'All'])->default('All')->after('document_type');
            }
            
            // Add Item Type column (Basah/Kering)
            if (!Schema::hasColumn('m_numbering_formats', 'item_type')) {
                $table->enum('item_type', ['Basah', 'Kering', 'All'])->default('All')->after('spb_type');
            }
        });

        // Update document_type enum to include PR
        DB::statement("ALTER TABLE m_numbering_formats DROP CONSTRAINT IF EXISTS m_numbering_formats_document_type_check");
        DB::statement("ALTER TABLE m_numbering_formats ADD CONSTRAINT m_numbering_formats_document_type_check CHECK (document_type IN ('SPB', 'SO', 'PO', 'DO', 'PR'))");

        // Drop old unique constraint first
        DB::statement("ALTER TABLE m_numbering_formats DROP CONSTRAINT IF EXISTS unique_store_doctype");
        
        // Create new unique index with new columns
        DB::statement("CREATE UNIQUE INDEX unique_store_doctype_spb_item ON m_numbering_formats (store_id, document_type, spb_type, item_type) WHERE delete_flag = false");
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        if (!Schema::hasTable('m_numbering_formats')) {
            return;
        }

        // Drop new unique index
        DB::statement("DROP INDEX IF EXISTS unique_store_doctype_spb_item");
        
        // Restore old unique constraint
        DB::statement("ALTER TABLE m_numbering_formats ADD CONSTRAINT unique_store_doctype UNIQUE (store_id, document_type) WHERE delete_flag = false");

        Schema::table('m_numbering_formats', function (Blueprint $table) {
            if (Schema::hasColumn('m_numbering_formats', 'item_type')) {
                $table->dropColumn('item_type');
            }
            
            if (Schema::hasColumn('m_numbering_formats', 'spb_type')) {
                $table->dropColumn('spb_type');
            }
        });

        // Restore original document_type enum
        DB::statement("ALTER TABLE m_numbering_formats DROP CONSTRAINT IF EXISTS m_numbering_formats_document_type_check");
        DB::statement("ALTER TABLE m_numbering_formats ADD CONSTRAINT m_numbering_formats_document_type_check CHECK (document_type IN ('SPB', 'SO', 'PO', 'DO'))");
    }
};
