<?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
    {
        // 1. Move existing data from reservations to reservation_items
        $reservations = DB::table('reservations')->get();
        foreach ($reservations as $reservation) {
            DB::table('reservation_items')->insert([
                'reservation_id' => $reservation->id,
                'product_id' => $reservation->product_id,
                'quantity' => $reservation->quantity,
                'price' => DB::table('products')->where('id', $reservation->product_id)->value('sale_price') ?? 0,
                'subtotal' => $reservation->total_price,
                'created_at' => $reservation->created_at,
                'updated_at' => $reservation->updated_at,
            ]);
        }

        // 2. Drop the redundant columns
        Schema::table('reservations', function (Blueprint $table) {
            $table->dropForeign(['product_id']);
            $table->dropColumn(['product_id', 'quantity']);
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::table('reservations', function (Blueprint $table) {
            $table->unsignedBigInteger('product_id')->nullable();
            $table->integer('quantity')->nullable();
            
            $table->foreign('product_id')->references('id')->on('products');
        });

        // Restore first item to the main table if possible
        $items = DB::table('reservation_items')->get()->groupBy('reservation_id');
        foreach($items as $reservation_id => $reservationItems) {
            $first = $reservationItems->first();
            DB::table('reservations')->where('id', $reservation_id)->update([
                'product_id' => $first->product_id,
                'quantity' => $first->quantity
            ]);
        }
    }
};
