<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Spatie\Permission\Models\Permission;
use Spatie\Permission\Models\Role;
use Spatie\Permission\PermissionRegistrar;

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('lines', function (Blueprint $table) {
            if (! Schema::hasColumn('lines', 'plan_start_date')) {
                $table->date('plan_start_date')->nullable()->after('current_plan_id');
            }
        });

        Schema::table('sales', function (Blueprint $table) {
            if (! Schema::hasColumn('sales', 'original_line_price')) {
                $table->decimal('original_line_price', 15, 2)->nullable()->after('line_price');
            }
            if (! Schema::hasColumn('sales', 'original_plan_price')) {
                $table->decimal('original_plan_price', 15, 2)->nullable()->after('plan_price');
            }
            if (! Schema::hasColumn('sales', 'line_price_overridden')) {
                $table->boolean('line_price_overridden')->default(false)->after('original_plan_price');
            }
            if (! Schema::hasColumn('sales', 'plan_price_overridden')) {
                $table->boolean('plan_price_overridden')->default(false);
            }
            if (! Schema::hasColumn('sales', 'override_reason')) {
                $table->string('override_reason')->nullable();
            }
            if (! Schema::hasColumn('sales', 'overridden_by')) {
                $table->foreignId('overridden_by')->nullable()->constrained('users')->nullOnDelete();
            }
        });

        if (Schema::hasTable('sales')) {
            DB::table('sales')->whereNull('original_line_price')->update([
                'original_line_price' => DB::raw('line_price'),
            ]);
            DB::table('sales')->whereNull('original_plan_price')->update([
                'original_plan_price' => DB::raw('plan_price'),
            ]);
        }

        if (! Schema::hasTable('line_price_history')) {
            Schema::create('line_price_history', function (Blueprint $table) {
                $table->id();
                $table->foreignId('line_id')->constrained('lines')->restrictOnDelete();
                $table->decimal('old_price', 15, 2);
                $table->decimal('new_price', 15, 2);
                $table->date('effective_date');
                $table->foreignId('user_id')->nullable()->constrained('users')->nullOnDelete();
                $table->string('reason')->nullable();
                $table->timestamps();
                $table->index(['line_id', 'effective_date']);
            });
        }

        if (Schema::hasTable('permissions')) {
            app()[PermissionRegistrar::class]->forgetCachedPermissions();
            $permission = Permission::findOrCreate('sales.override_price', 'web');
            foreach (['Administrator', 'Manager'] as $roleName) {
                $role = Role::query()->where('name', $roleName)->where('guard_name', 'web')->first();
                $role?->givePermissionTo($permission);
            }
        }
    }

    public function down(): void
    {
        Schema::dropIfExists('line_price_history');

        Schema::table('sales', function (Blueprint $table) {
            if (Schema::hasColumn('sales', 'overridden_by')) {
                $table->dropConstrainedForeignId('overridden_by');
            }
            foreach (['original_line_price', 'original_plan_price', 'line_price_overridden', 'plan_price_overridden', 'override_reason'] as $column) {
                if (Schema::hasColumn('sales', $column)) {
                    $table->dropColumn($column);
                }
            }
        });

        Schema::table('lines', function (Blueprint $table) {
            if (Schema::hasColumn('lines', 'plan_start_date')) {
                $table->dropColumn('plan_start_date');
            }
        });
    }
};
