<?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
    {
        Schema::create('m_numbering_formats', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('store_id');
            $table->enum('document_type', ['SPB', 'SO', 'PO', 'DO']);
            $table->string('format_pattern', 255);
            $table->string('prefix', 20)->nullable();
            $table->string('suffix', 20)->nullable();
            $table->string('separator', 5)->default('-');
            $table->integer('number_length')->default(4);
            $table->enum('reset_period', ['Never', 'Daily', 'Monthly', 'Yearly'])->default('Monthly');
            $table->integer('current_number')->default(0);
            $table->date('last_reset_date')->nullable();
            $table->boolean('is_active')->default(true);
            $table->boolean('delete_flag')->default(false);
            $table->unsignedBigInteger('created_by')->nullable();
            $table->unsignedBigInteger('updated_by')->nullable();
            $table->timestamps();

            $table->foreign('store_id')->references('id')->on('m_stores');
            $table->unique(['store_id', 'document_type', 'delete_flag'], 'unique_store_doctype');
            $table->index(['delete_flag', 'is_active']);
            $table->index(['store_id', 'document_type']);
        });

        // Insert sample numbering formats
        $stores = DB::table('m_stores')->where('delete_flag', false)->get();
        
        foreach ($stores as $store) {
            // SPB Format for each store
            DB::table('m_numbering_formats')->insert([
                'store_id' => $store->id,
                'document_type' => 'SPB',
                'format_pattern' => '{DOCTYPE}{SEP}{STORE}{SEP}{YY}{MM}{SEP}{NUMBER}',
                'prefix' => '',
                'suffix' => '',
                'separator' => '-',
                'number_length' => 4,
                'reset_period' => 'Monthly',
                'current_number' => 0,
                'last_reset_date' => now()->format('Y-m-d'),
                'is_active' => true,
                'delete_flag' => false,
                'created_at' => now(),
                'updated_at' => now()
            ]);

            // SO Format for each store
            DB::table('m_numbering_formats')->insert([
                'store_id' => $store->id,
                'document_type' => 'SO',
                'format_pattern' => '{DOCTYPE}{SEP}{STORE}{SEP}{YYYY}{MM}{DD}{SEP}{NUMBER}',
                'prefix' => '',
                'suffix' => '',
                'separator' => '-',
                'number_length' => 3,
                'reset_period' => 'Daily',
                'current_number' => 0,
                'last_reset_date' => now()->format('Y-m-d'),
                'is_active' => true,
                'delete_flag' => false,
                'created_at' => now(),
                'updated_at' => now()
            ]);
        }

        // Add some alternative formats for demonstration
        $firstStore = $stores->first();
        if ($firstStore) {
            // PO Format
            DB::table('m_numbering_formats')->insert([
                'store_id' => $firstStore->id,
                'document_type' => 'PO',
                'format_pattern' => 'PO{SEP}{STORE}{SEP}{YY}{SEP}{NUMBER}',
                'prefix' => '',
                'suffix' => '',
                'separator' => '-',
                'number_length' => 5,
                'reset_period' => 'Yearly',
                'current_number' => 0,
                'last_reset_date' => now()->format('Y-m-d'),
                'is_active' => true,
                'delete_flag' => false,
                'created_at' => now(),
                'updated_at' => now()
            ]);

            // DO Format
            DB::table('m_numbering_formats')->insert([
                'store_id' => $firstStore->id,
                'document_type' => 'DO',
                'format_pattern' => '{STORE}{DOCTYPE}{YY}{MM}{DD}{NUMBER}',
                'prefix' => '',
                'suffix' => '',
                'separator' => '',
                'number_length' => 3,
                'reset_period' => 'Daily',
                'current_number' => 0,
                'last_reset_date' => now()->format('Y-m-d'),
                'is_active' => true,
                'delete_flag' => false,
                'created_at' => now(),
                'updated_at' => now()
            ]);
        }
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('m_numbering_formats');
    }
};