<?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.
     * Migrate existing single restaurant data to multi-restaurant structure
     */
    public function up(): void
    {
        // Step 1: Create a default restaurant from existing branches (warehouses renamed to branches)
        // Check if branches table exists, otherwise use warehouses
        $branchTable = Schema::hasTable('branches') ? 'branches' : 'warehouses';
        
        if (!Schema::hasTable($branchTable)) {
            return; // No data to migrate
        }
        
        // Get unique restaurant codes from branches
        $branches = DB::table($branchTable)->get();
        
        if ($branches->isEmpty()) {
            return; // No data to migrate
        }

        // Group branches by restaurant_code if exists, otherwise create one restaurant
        $restaurantGroups = $branches->groupBy('restaurant_code');
        
        $restaurantMap = [];
        
        foreach ($restaurantGroups as $restaurantCode => $group) {
            $code = $restaurantCode ?: 'REST001';
            
            // Check if restaurant already exists
            $existingRestaurant = DB::table('restaurants')->where('code', $code)->first();
            
            if ($existingRestaurant) {
                $restaurantId = $existingRestaurant->id;
            } else {
                // Create restaurant
                $restaurantId = DB::table('restaurants')->insertGetId([
                    'name' => $group->first()->name . ' Restaurant',
                    'code' => $code,
                    'status' => 'active',
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
            }

            $restaurantMap[$restaurantCode ?: 'default'] = $restaurantId;

            // Update branches with restaurant_id
            DB::table($branchTable)
                ->whereIn('id', $group->pluck('id'))
                ->update(['restaurant_id' => $restaurantId]);
        }

        // Step 2: Populate restaurant_id in all tables based on warehouse_id relationships
        $tablesToUpdate = [
            'users' => 'warehouse_id',
            'agents' => 'warehouse_id',
            'stock_outs' => 'warehouse_id',
            'stock_closings' => 'warehouse_id',
            'waiters' => 'warehouse_id',
            'pos_tables' => 'warehouse_id',
            'pos_menu_items' => 'warehouse_id',
            'pos_orders' => 'warehouse_id',
            'restaurant_menu_categories' => 'warehouse_id',
            'pos_customers' => 'warehouse_id',
        ];

        foreach ($tablesToUpdate as $tableName => $warehouseColumn) {
            if (Schema::hasTable($tableName) && Schema::hasColumn($tableName, $warehouseColumn)) {
                // Update restaurant_id based on warehouse_id -> restaurant relationship
                // Use branches table (warehouses was renamed to branches)
                $branchTable = Schema::hasTable('branches') ? 'branches' : 'warehouses';
                if (Schema::hasTable($branchTable)) {
                    DB::statement("
                        UPDATE {$tableName} t
                        INNER JOIN {$branchTable} w ON t.{$warehouseColumn} = w.id
                        SET t.restaurant_id = w.restaurant_id
                        WHERE t.{$warehouseColumn} IS NOT NULL
                        AND w.restaurant_id IS NOT NULL
                    ");
                }
            }
        }

        // Step 3: Update inventories - get restaurant_id from items or categories
        if (Schema::hasTable('inventories')) {
            // Try to get restaurant_id from item_id -> items -> restaurant_id
            DB::statement("
                UPDATE inventories i
                INNER JOIN items it ON i.item_id = it.id
                SET i.restaurant_id = it.restaurant_id
                WHERE i.item_id IS NOT NULL
                AND it.restaurant_id IS NOT NULL
            ");

            // For inventories without item_id, try category_id -> inventory_categories -> restaurant_id
            DB::statement("
                UPDATE inventories i
                INNER JOIN inventory_categories ic ON i.category_id = ic.id
                SET i.restaurant_id = ic.restaurant_id
                WHERE i.item_id IS NULL
                AND i.category_id IS NOT NULL
                AND ic.restaurant_id IS NOT NULL
                AND i.restaurant_id IS NULL
            ");
        }

        // Step 4: Update pos_order_items - get restaurant_id from order_id
        if (Schema::hasTable('pos_order_items')) {
            $hasBranchId = Schema::hasColumn('pos_order_items', 'branch_id');
            if ($hasBranchId) {
                DB::statement("
                    UPDATE pos_order_items poi
                    INNER JOIN pos_orders po ON poi.order_id = po.id
                    SET poi.restaurant_id = po.restaurant_id,
                        poi.branch_id = po.branch_id
                    WHERE po.restaurant_id IS NOT NULL
                ");
            } else {
                DB::statement("
                    UPDATE pos_order_items poi
                    INNER JOIN pos_orders po ON poi.order_id = po.id
                    SET poi.restaurant_id = po.restaurant_id
                    WHERE po.restaurant_id IS NOT NULL
                ");
            }
        }

        // Step 5: Update menu_item_recipes - get restaurant_id from menu_item_id
        if (Schema::hasTable('menu_item_recipes')) {
            DB::statement("
                UPDATE menu_item_recipes mir
                INNER JOIN pos_menu_items pmi ON mir.menu_item_id = pmi.id
                SET mir.restaurant_id = pmi.restaurant_id
                WHERE pmi.restaurant_id IS NOT NULL
            ");
        }

        // Step 6: Update recipe_usage_logs - get restaurant_id from order_id
        if (Schema::hasTable('recipe_usage_logs')) {
            $hasBranchId = Schema::hasColumn('recipe_usage_logs', 'branch_id');
            if ($hasBranchId) {
                DB::statement("
                    UPDATE recipe_usage_logs rul
                    INNER JOIN pos_orders po ON rul.order_id = po.id
                    SET rul.restaurant_id = po.restaurant_id,
                        rul.branch_id = po.branch_id
                    WHERE po.restaurant_id IS NOT NULL
                ");
            } else {
                DB::statement("
                    UPDATE recipe_usage_logs rul
                    INNER JOIN pos_orders po ON rul.order_id = po.id
                    SET rul.restaurant_id = po.restaurant_id
                    WHERE po.restaurant_id IS NOT NULL
                ");
            }
        }

        // Step 7: Create restaurant owner user from existing admin if needed
        // This is optional - you may want to do this manually
        $adminUser = DB::table('users')->where('role', 'admin')->first();
        if ($adminUser && !$adminUser->restaurant_id) {
            // Assign first restaurant to admin (or create restaurant owner)
            $firstRestaurant = DB::table('restaurants')->first();
            if ($firstRestaurant) {
                // Option 1: Update admin to have restaurant_id
                // DB::table('users')->where('id', $adminUser->id)->update(['restaurant_id' => $firstRestaurant->id]);
                
                // Option 2: Create restaurant owner user (commented out - do manually if needed)
                // DB::table('users')->insert([
                //     'name' => $adminUser->name . ' (Restaurant Owner)',
                //     'email' => 'owner@restaurant.com',
                //     'password' => $adminUser->password,
                //     'role' => 'restaurant_owner',
                //     'restaurant_id' => $firstRestaurant->id,
                //     'created_at' => now(),
                //     'updated_at' => now(),
                // ]);
            }
        }
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // Remove restaurant_id from all tables
        $tables = [
            'users',
            'agents',
            'stock_outs',
            'stock_closings',
            'waiters',
            'pos_tables',
            'pos_menu_items',
            'pos_orders',
            'restaurant_menu_categories',
            'pos_customers',
            'inventories',
            'pos_order_items',
            'menu_item_recipes',
            'recipe_usage_logs',
            'items',
            'inventory_categories',
        ];

        foreach ($tables as $tableName) {
            if (Schema::hasTable($tableName) && Schema::hasColumn($tableName, 'restaurant_id')) {
                DB::table($tableName)->update(['restaurant_id' => null]);
            }
        }

        // Remove restaurants
        DB::table('restaurants')->truncate();
    }
};
