<?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.
     * 
     * This migration:
     * 1. Migrates existing order data to order_items
     * 2. Removes product-specific columns from orders table
     */
    public function up(): void
    {
        // Step 1: Migrate existing data to order_items
        $orders = DB::table('orders')->get();
        
        foreach ($orders as $order) {
            DB::table('order_items')->insert([
                'order_id' => $order->id,
                'product_id' => null,
                'product_name' => 'Cetak', // Default product name
                'material_id' => null,
                'material_name' => $order->material,
                'file_name' => $order->file_name,
                'file_path' => $order->file_path,
                'size_description' => $order->size,
                'size_value' => 1, // Default, can't calculate from string
                'size_unit' => 'm2',
                'qty' => $order->qty,
                'base_price' => $order->unit_price,
                'calculated_price' => $order->unit_price,
                'subtotal' => $order->total_price,
                'finishing_note' => $order->finishing_note,
                'created_at' => $order->created_at,
                'updated_at' => $order->updated_at,
            ]);
        }

        // Step 2: Remove product-specific columns from orders
        Schema::table('orders', function (Blueprint $table) {
            $table->dropColumn([
                'file_name',
                'file_path', 
                'size',
                'material',
                'qty',
                'finishing_note',
                'unit_price',
            ]);
        });

        // Add notes column for order-level notes
        Schema::table('orders', function (Blueprint $table) {
            $table->text('notes')->nullable()->after('customer_phone');
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // Re-add columns to orders
        Schema::table('orders', function (Blueprint $table) {
            $table->dropColumn('notes');
            $table->string('file_name')->nullable()->after('customer_phone');
            $table->string('file_path')->nullable()->after('file_name');
            $table->string('size')->nullable()->after('file_path');
            $table->string('material')->nullable()->after('size');
            $table->integer('qty')->default(1)->after('material');
            $table->text('finishing_note')->nullable()->after('qty');
            $table->decimal('unit_price', 15, 2)->default(0)->after('finishing_note');
        });

        // Migrate data back from order_items to orders (only first item per order)
        $orderItems = DB::table('order_items')
            ->select('order_items.*')
            ->join(
                DB::raw('(SELECT order_id, MIN(id) as min_id FROM order_items GROUP BY order_id) as first_items'),
                'order_items.id',
                '=',
                'first_items.min_id'
            )
            ->get();

        foreach ($orderItems as $item) {
            DB::table('orders')
                ->where('id', $item->order_id)
                ->update([
                    'file_name' => $item->file_name,
                    'file_path' => $item->file_path,
                    'size' => $item->size_description,
                    'material' => $item->material_name,
                    'qty' => $item->qty,
                    'finishing_note' => $item->finishing_note,
                    'unit_price' => $item->base_price,
                ]);
        }
    }
};
