<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     * 
     * Add 'type' field to categories table to support food/drink grouping in reports
     */
    public function up(): void
    {
        Schema::table('categories', function (Blueprint $table) {
            $table->enum('type', ['food', 'drink', 'snack', 'dessert', 'other'])
                  ->after('name')
                  ->default('other')
                  ->comment('Category type for reporting and analytics');
        });

        // Auto-classify existing categories based on common naming patterns
        // This is a best-effort classification - admin can adjust via UI later
        DB::statement("UPDATE categories SET type = 'drink' WHERE LOWER(name) LIKE '%coffee%' OR LOWER(name) LIKE '%juice%' OR LOWER(name) LIKE '%tea%' OR LOWER(name) LIKE '%drink%' OR LOWER(name) LIKE '%beverage%' OR LOWER(name) LIKE '%smoothie%' OR LOWER(name) LIKE '%soda%'");
        
        DB::statement("UPDATE categories SET type = 'food' WHERE LOWER(name) LIKE '%food%' OR LOWER(name) LIKE '%rice%' OR LOWER(name) LIKE '%noodle%' OR LOWER(name) LIKE '%pasta%' OR LOWER(name) LIKE '%burger%' OR LOWER(name) LIKE '%sandwich%' OR LOWER(name) LIKE '%meal%' OR LOWER(name) LIKE '%breakfast%' OR LOWER(name) LIKE '%lunch%' OR LOWER(name) LIKE '%dinner%'");
        
        DB::statement("UPDATE categories SET type = 'snack' WHERE LOWER(name) LIKE '%snack%' OR LOWER(name) LIKE '%chips%' OR LOWER(name) LIKE '%fries%' OR LOWER(name) LIKE '%appetizer%' OR LOWER(name) LIKE '%finger%'");
        
        DB::statement("UPDATE categories SET type = 'dessert' WHERE LOWER(name) LIKE '%dessert%' OR LOWER(name) LIKE '%cake%' OR LOWER(name) LIKE '%ice cream%' OR LOWER(name) LIKE '%sweet%' OR LOWER(name) LIKE '%pastry%'");
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::table('categories', function (Blueprint $table) {
            $table->dropColumn('type');
        });
    }
};
