<?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
{
    public function up(): void
    {
        Schema::create('expense_categories', function (Blueprint $table) {
            $table->id();
            $table->string('name', 100)->unique();
            $table->string('color', 20)->nullable()->default('#6b7280');
            $table->string('description', 255)->nullable();
            $table->boolean('is_active')->default(true);
            $table->timestamps();
        });

        // Seed default categories
        $defaults = [
            ['name' => 'Rent',            'color' => '#ef4444', 'description' => 'Office, shop or warehouse rent'],
            ['name' => 'Salary',          'color' => '#f97316', 'description' => 'Staff wages and salaries'],
            ['name' => 'Utilities',       'color' => '#eab308', 'description' => 'Electricity, water, internet'],
            ['name' => 'Transport',       'color' => '#22c55e', 'description' => 'Fuel, logistics and delivery'],
            ['name' => 'Marketing',       'color' => '#06b6d4', 'description' => 'Advertising and promotions'],
            ['name' => 'Equipment',       'color' => '#3b82f6', 'description' => 'Tools and equipment purchases'],
            ['name' => 'Maintenance',     'color' => '#8b5cf6', 'description' => 'Repairs and upkeep'],
            ['name' => 'Office Supplies', 'color' => '#ec4899', 'description' => 'Stationery and office consumables'],
            ['name' => 'Inventory',       'color' => '#14b8a6', 'description' => 'Stock and raw materials'],
            ['name' => 'Miscellaneous',   'color' => '#f59e0b', 'description' => 'Other expenses'],
        ];

        $now = now();
        $rows = [];
        foreach ($defaults as $d) {
            $rows[] = array_merge($d, ['is_active' => true, 'created_at' => $now, 'updated_at' => $now]);
        }
        DB::table('expense_categories')->insert($rows);

        // Import any existing expense categories not already in the list
        if (Schema::hasTable('expenses')) {
            $existing = DB::table('expenses')
                ->whereNotNull('category')
                ->where('category', '!=', '')
                ->distinct()
                ->pluck('category');

            $fallbackColors = ['#ef4444','#f97316','#eab308','#22c55e','#06b6d4','#3b82f6','#8b5cf6','#ec4899','#14b8a6','#f59e0b'];
            $colorIndex = count($defaults);

            foreach ($existing as $cat) {
                if (!DB::table('expense_categories')->where('name', $cat)->exists()) {
                    DB::table('expense_categories')->insert([
                        'name'        => $cat,
                        'color'       => $fallbackColors[$colorIndex % count($fallbackColors)],
                        'is_active'   => true,
                        'created_at'  => $now,
                        'updated_at'  => $now,
                    ]);
                    $colorIndex++;
                }
            }
        }
    }

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