<?php

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

return new class extends Migration {
    public function up(): void
    {
        Schema::table('crm_files', function (Blueprint $table) {
            $table->foreignId('parent_file_id')->nullable()->index();
            $table->foreignId('root_file_id')->nullable()->index();
            $table->string('checksum', 64)->nullable()->index();
            $table->string('access_level')->default('business')->index();
            $table->boolean('is_current')->default(true)->index();
        });
        DB::table('crm_files')->orderBy('id')->each(function ($file) {
            DB::table('crm_files')->where('id', $file->id)->update([
                'root_file_id' => $file->id, 'is_current' => true,
            ]);
        });

        Schema::create('crm_file_permissions', function (Blueprint $table) {
            $table->id(); $table->foreignId('crm_file_id')->index(); $table->foreignId('user_id')->index();
            $table->boolean('can_view')->default(true); $table->boolean('can_download')->default(true);
            $table->boolean('can_replace')->default(false); $table->boolean('can_delete')->default(false);
            $table->foreignId('granted_by')->nullable()->index(); $table->timestamps();
            $table->unique(['crm_file_id', 'user_id']);
        });
        Schema::create('crm_file_access_logs', function (Blueprint $table) {
            $table->id(); $table->foreignId('business_id')->index(); $table->foreignId('crm_file_id')->nullable()->index();
            $table->foreignId('user_id')->nullable()->index(); $table->string('action')->index();
            $table->boolean('allowed')->default(true); $table->string('ip_address', 45)->nullable();
            $table->text('user_agent')->nullable(); $table->json('metadata')->nullable();
            $table->timestamp('occurred_at')->index(); $table->timestamps();
        });

        foreach (['view_crm_files', 'download_crm_files', 'manage_crm_files', 'grant_crm_file_access'] as $permission) {
            DB::table('permissions')->insertOrIgnore([
                'name' => $permission, 'guard_name' => 'web', 'created_at' => now(), 'updated_at' => now(),
            ]);
            $permissionId = DB::table('permissions')->where('name', $permission)->value('id');
            foreach (DB::table('roles')->whereRaw('LOWER(name) = ?', ['admin'])->pluck('id') as $roleId) {
                DB::table('role_has_permissions')->insertOrIgnore(['permission_id' => $permissionId, 'role_id' => $roleId]);
            }
        }
    }

    public function down(): void
    {
        Schema::dropIfExists('crm_file_access_logs');
        Schema::dropIfExists('crm_file_permissions');
        Schema::table('crm_files', fn (Blueprint $table) => $table->dropColumn([
            'parent_file_id', 'root_file_id', 'checksum', 'access_level', 'is_current',
        ]));
    }
};
