<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        // First, let's create a mapping of old item IDs to new account IDs
        // We'll create new account records for the old items that don't have accounts yet

        $oldItems = DB::table('items')->get();
        $itemMapping = [];

        foreach ($oldItems as $oldItem) {
            // Check if this item already has an account
            $existingAccount = DB::table('accounts')
                ->where('model_type', 'Item')
                ->where('name', $oldItem->name)
                ->first();

            if ($existingAccount) {
                // Map old item ID to existing account ID
                $itemMapping[$oldItem->id] = $existingAccount->id;
            } else {
                // Create new account for this item
                $accountId = DB::table('accounts')->insertGetId([
                    'name' => $oldItem->name,
                    'sku' => $oldItem->sku ?? 'OLD-' . $oldItem->id,
                    'barcode' => $oldItem->barcode,
                    'reorder_level' => $oldItem->reorder_level ?? 0,
                    'status' => $oldItem->status ?? 'active',
                    'is_active' => $oldItem->is_active ?? true,
                    'model_type' => 'Item',
                    'model_id' => 0, // Will be updated after creation
                    'chart_of_account_id' => 52, // Raw Material Chart of Account ID
                    'code' => 'ITEM-' . $oldItem->id,
                    'description' => 'Migrated from old items table',
                    'balance' => 0.00,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);

                // Update model_id to self-reference
                DB::table('accounts')
                    ->where('id', $accountId)
                    ->update(['model_id' => $accountId]);

                $itemMapping[$oldItem->id] = $accountId;
            }
        }

        // Now update all tables that reference item_id to use the new account IDs
        $tablesToUpdate = [
            'purchase_request_items',
            'purchase_order_items',
            'grn_items',
            'gate_pass_items',
            'qc_items',
            'stock_transfer_items',
            'inventory_balances',
            'inventory_moves',
            'stock_balances',
            'item_suppliers',
            'item_variations',
            'boms',
            'quality_checks',
            'wo_material_issues',
            'wo_receipts',
        ];

        foreach ($tablesToUpdate as $table) {
            if (Schema::hasTable($table) && Schema::hasColumn($table, 'item_id')) {
                foreach ($itemMapping as $oldItemId => $newAccountId) {
                    DB::table($table)
                        ->where('item_id', $oldItemId)
                        ->update(['item_id' => $newAccountId]);
                }
            }
        }

        // Also update any other references to items table
        if (Schema::hasTable('bom_items') && Schema::hasColumn('bom_items', 'item_id')) {
            foreach ($itemMapping as $oldItemId => $newAccountId) {
                DB::table('bom_items')
                    ->where('item_id', $oldItemId)
                    ->update(['item_id' => $newAccountId]);
            }
        }
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // This migration is not easily reversible as it creates new data
        // In a real scenario, you would need to maintain the mapping
        echo "This migration cannot be easily reversed as it creates new account records.\n";
    }
};
